Select number not in a column

Database
Enthusiast

Select number not in a column

Hi, i have a table A with column 'ID' that holds positive integer values

Now i want to find the minimum value not in this column and less than 10 (say)

A.ID

1

2

4

8

9

12

psuedo code:

Select <number> where <number> > 0 and <number> < 10 and <number> not in (select ID from A where ID < 10)

How to write this query?

The result should be '5' for this data

One way of doing this is have table B with all positive integer values and say:

select ID from B where ID < 10 and ID not in (select ID from A where ID < 10)

Is there a way without having to use table B?

Thanks,

-srinivas yelamanchili

Tags (1)
1 REPLY
Senior Supporter

Re: Select number not in a column

didn't run it but I guess this will work

select min(id) + 1

from

(

select id, min(id) over (order by 1 rows between 1 following and 1 following) as next_id

from tableA

qualify id+1 <> next_id

) as tmp

and would expect 3 in your example...