user6615498 user6615498 - 4 months ago 8
SQL Question

Select first missing id above 0

I have in my columns (ID) values

5
6
9


I want to select first missing IDfrom above 0. My desire select value will be 1.(if 1 exists then it will selects 2 and so on...).
I'm using this code:

SELECT MIN(id) As MinMissingId FROM table1 where id>=0


But my result is first existing ID and not missing

Answer

This will return the next unused id starting with 1, works in all cases, e.g. table is empty or there's no gap:

WITH cte AS
 (
   SELECT id FROM tab
   UNION ALL 
   SELECT 0
 )
SELECT MIN(id) + 1
FROM cte
WHERE NOT EXISTS
 (
   SELECT * 
   FROM tab 
   WHERE tab.id = cte.id + 1
 )
Comments