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