Thread

Finding 'holes'

Magnus Hagander <mha@edu.sollentuna.se> — 1998-05-18T14:52:29Z

Hi!

I need (well, not really need, but want to) a query that can give me the
"holes" in a sequence.
For example, I have a table which contains
id		data
--		----
1		xxx
2		yyy
4		zzz
6		...

I would then like a query that can return the values 3 and 5, since
those are the ones not existing in the table.
I guess I could create a table which contains all the values 1..max(id)
and then do a 
SELECT id FROM all_ids WHERE id NOT IN (SELECT id FROM datatable)

but I would really like to make it without the need to create such a
table (which will in time be very large and unnecessary).

Does anybody know of a way to do this?

//Magnus