How can I find the minimum value among the values not filled or not exists
for example
001
002
003
013
015
result must return 004
select min
Add a comment
|
5 Answers
declare @T table(Number int)
insert into @T values (1),(2),(3),(13),(15)
select top 1 Number + 1
from @T
where Number + 1 not in (select Number from @T)
order by Number
Update:
A version using char(3) zero padded.
declare @T table(ID char(3))
insert into @T values ('001'),('002'),('003'),('013'),('015')
select top 1 right(1001 + Id, 3)
from @T
where Id + 1 not in (select Id from @T)
order by Id
2 Comments
Seph
This usually works quite well, but how would this work if
009 was stored as a CHAR(3)? the leading zeroes in the question makes me think it's a CHAR not INT field. Just wondering :)
Mikael Eriksson
@Seph - Added a version with char(3) as data type. No difference really except for the result value and the fact that there are a bunch of implicit type conversions and it will not use any index on
Number.Assuming your sequence in the table YourNumbersTable Try this (SQL Server 2005+):
declare @min int, @max int
select @min = MIN(Id), @max = MAX(Id) from YourNumbersTable
;WITH numbers(id) as
(
SELECT @min id
UNION ALL
SELECT id+1
FROM numbers
WHERE id <= @max
)
SELECT MIN(Numbers.id)
FROM Numbers
LEFT JOIN YourNumbersTable ON Numbers.Id = YourNumbersTable.Id
WHERE YourNumbersTable.Id IS NULL
OPTION(MAXRECURSION 0)
Comments
Try this (no joins, no rec-cte)
declare @T table(n int)
insert into @T values (1),(2),(3),(13),(15)
select max(n)+1 from (
select *,l=n-row_number() over(order by n)
from (
select n from @T
union
select 0 -- what about 0 ??
) as s
) as a
where l=-1
Comments
This is similar to what @szauri has suggested, but without aggregating:
;
WITH ranked AS (
SELECT n, r = ROW_NUMBER() OVER (ORDER BY n)
FROM @T
)
SELECT TOP 1 r
FROM ranked
WHERE n <> r
ORDER BY n
Note: Both @szauri's and my solutions require SQL Server 2005 or later version.
Comments
maybe I have an alternative solution. It can be slower on wide number ranges because of the loop in it.
-- prepare a table to have your example
declare @ref table (id int)
insert into @ref (id) values (1), (2), (3), (13), (15)
-- this will return 1
select min(id) from @ref
-- magic happens here
declare @i int, @max int, @returnValue int
select @i = min(id), @max = max(id) from @ref
declare @tmp table (id int)
while @i <= @max
begin
insert into @tmp (id) values (@i)
set @i = @i + 1
end
select @returnValue = min(t.id)
from @tmp t left outer join @ref r on t.id = r.id
where r.id is null
-- this will return 4
select @returnValue
Comments
