How to write query? 2

[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?

 

[Dagon]

A couple of suggestions:

select min(num)+1
from test_num t1
where not exists
(select 1 from test_num t2
where t2.num + 1= t1.num)

select min(rnum) from
(select rownum as rnum from all_objects)
where rnum not in (select num from test_num)

 

[hoinz]

another idea:

select min(x) from
(
select 1 as x from dual
UNION
select num+1 from table
MINUS
select num from table)
/

Dagon:
I think your first suggestion will not work, if 1 is not in the table.

regards

 

[Dagon]

Yes, I was aware of that, which is why I gave an alternative. Actually, you could probably do the second example more simply using something like:

select min(rnum) from
(select num, rownum as rnum from test_num)
where num <> rnum

 

[Gjj]

Thank you

 

[hoinz]

Dagon,

I disagree with your change of your second example.
You are making assumptions about the order how data is stored.
And yes, there is a workaround. But this will not make the example simple.


Gjj,

glad it helped, thanks

regards

 

[Dagon]

Fair point. Change it to:

select min(rnum) from
(select num, rownum as rnum from 
(select * from test_num order by num))
where num <> rnum

 

[hoinz]

ok, simple enough, I think.