The following is one answer to this Problem of the Week that works in PostgreSQL (and probably in other flavors of SQL):

SELECT id
FROM (SELECT id + 1 AS id FROM stars UNION SELECT 1 AS id) t
WHERE NOT EXISTS (SELECT s.id FROM stars AS s WHERE s.id = t.id)
ORDER BY id
LIMIT 1;

1 Comment

Andrew C · August 15, 2012 at 7:19 PM

select x.newid from
(select level newid from dual
connect by level <
(select max(oldid) from sparse_table)
) x
left join sparse_tablec on c.oldid= x.newid
where c.oldid is null

— using Oracle 10

Leave a Reply

Your email address will not be published. Required fields are marked *