Finding missing numbers in a sequence prior to first existing number

Databases
1

I have been using this (well circulated) method to ascertain the first missing number in a sequence from a selection of IDs from a specific column (‘song_name’) in a table (‘song’) so as to reassign deleted IDs to future entries (these field names are just examples):-

SELECT l.id+1 AS start
FROM (SELECT id FROM song WHERE song_name LIKE ‘imagine’) AS l
LEFT OUTER JOIN (SELECT id FROM song WHERE song_name LIKE ‘imagine’) AS r ON l.id+1 = r.id
WHERE r.id IS NULL
ORDER BY start ASC LIMIT 1;

ID	SONG
1	Imagine
2	Imagine
3	Imagine
6	Imagine
7	Imagine
8	Imagine
1	Eleanor Rigby
2	Eleanor Rigby
3	Eleanor Rigby

If the sequence of IDs is: 1,2,3,6,7,8, then it will correctly return 4 as the result.

However, if the sequence is: 2,3,6,7,8, then it will still return 4 as the result, even though 1 is the first missing number (it only finds the first missing number from after the sequence starts, i.e. if the sequence is 50,51,52,54,55, then the result would be 53, disregarding the missing IDs of 1 to 49).

My solution to this would be to first run a separate command to check specifically if 1 is found in the selected IDs, and if not then assign 1 as the ID. But if 1 is found, then run the above command, as any gap in the sequence following 1 would definitely be the first.

But I am wondering if there is a more elegant solution that I have not thought of? Perhaps using the WHERE EXISTS / WHERE NOT EXISTS command?

2

yes, there is, and it is to completely disregard the gaps in the numbers, and stop trying to renumber them after deletions

3

Yes, having thought over it, re-assigning deleted IDs would be a bad idea from the point of view that people saving URLs (which will include an ID) would then get a page featuring a different song rather than some sort of ‘entry deleted’ message (the latter obviously being the preferred outcome).

This is the code I will be using to identify the next available ID (I am not using AUTO_INCREMENT for this column as I want an individual ID count for each different song) (credit to Lukáš Lalinský at Stack Overflow):-

SELECT 1 + coalesce ((SELECT MAX(id) FROM song
WHERE song_name LIKE ‘$song_name’), 0) AS id;

However, it seems people might still need to identify the beginning of all gaps in a sequence (see Matthew Frederick’s comment), for which the below code does the trick:-

SELECT t1.id+1 AS id FROM song t1
WHERE NOT EXISTS (SELECT 1 FROM song t2 WHERE t2.id = t1.id+1
AND song_name LIKE ‘$song_name’)
UNION
SELECT 1 AS id FROM song
WHERE NOT EXISTS (SELECT 1 FROM song WHERE id = 1
AND song_name LIKE ‘$song_name’)
ORDER BY id ASC LIMIT 1;

This is taken from Darrell Miller’s solution posted on Stack Overflow. I had a problem with the TOP function (apparently [URL=“http://www.w3schools.com/sql/sql_top.asp”]not all database systems support the TOP clause), but using ORDER BY id ASC LIMIT 1 at the end of the command will do the same thing.