How to write query?

[Gjj]

I have table

Number Some data
1 x
3 y
4 s
5 x

I would like to find smallest number starting from 1 which is not in the table.

In this case that would be 2.
Does somebody have idea how to write query?

 

[johnherman]

select min(number) from table where number > 1

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[johnherman]

oops, wrong answer - disregard.


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Gjj]

You wont get good result with that query

 

[earthandfire]

I'm sure there is a more efficient way of doing it, but this seems to work:

declare @counter int
declare @result int
declare @tmp int
declare @done bit

set @result = 0
set @done = 0
set @counter = 1
while @done = 0
begin
	set @tmp = coalesce((select field1 from table1 where field1 = @counter),0)
	if @tmp <> 0 set @result = @tmp else set @done = 1
	set @counter = @counter + 1
end

select @result + 1

Hope this helps

 

[SQLDenis]

create a number table
left join with that table or
select min(number) from table where not in (select number from numberTable)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

select min(missing) as first_gap
  from (
       select t1.number + 1 as missing
         from Ihavetable t1
       left outer
         join Ihavetable t2
           on t1.number + 1 = t2.number
        where t2.number is null      
       ) as strahan

r937.com | rudy.ca

 

[vongrunt]

How about:

declare @N int; set @N = 0
select @N = @N+1-abs(sign(Number-1-@N)) from blah
select nullif(@N, max(Number)) + 1 from blah

... assuming that Number is covered by clustered index?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)