SQL Get the missing IDs

Question

Possible Duplicate:
Find missing values
How do I get first unused ID in the table?

   myTable
-----+---------
 ID  | Value
-----+---------
 7   | Foo
 9   | Bar
 19  | Etc

I need to do a query here to get the missing ID (number 1), the result will be only the first row (result), maybe using LIMIT 1, and then insert using this ID. So, it will look this:

   myTable
-----+---------
 ID  | Value
-----+---------
 1   | Fir
 7   | Foo
 9   | Bar
 19  | Etc

Now on the next query it will result only the number 2.

Notes:
- I am using MySQL 5.1.32

are you trying to do this as a pure SQL statement or as some part of a larger script/program? — cpjolicoeur, Mar 26 '12 at 20:17

score 0 · Answer 1 · edited May 23 '17 at 12:02

EDIT: Answered before he let us know what database he was using... but this answer tells how to create a temp table in MySQL, and the basic idea should still work: Create table variable in MySQL

This should work for T-SQL:

DECLARE @GapTable
(
    ID int IDENTITY(1,1),
    OtherTableID int    
)

INSERT INTO @gapTable (OtherTableID)
SELECT OtherTableID = ID
FROM OtherTable
ORDER BY ID


SELECT MIN(ID)
FROM @GapTable
WHERE ID <> OtherTableID

Basically you create a table variable and insert everything into it. This should give you a known good list of ID values. Then you can just select out the first value where the generated ID doesn't match the existing one and you'll know what you can use.

SQL Get the missing IDs

1 Answers1