Find missing numbers in a specific numeric field for all rows
In a table, there is a numeric field which contains an inexhaustive list of values
e.g. 1,2,3,5,9,13.
Is there a way to list all values which are NOT in the list using an SQL Query (not cursor)
i.e. 4,6,7,8,10,11 etc
The solution so far is to generate the existing value + 1 ensuring that it is not in the list.
SELECT DISTINCT e1_cd_aff + 1 AS NUMINDEX
FROM affair_raw_ref
WHERE e1_cd_aff + 1 NOT IN
(SELECT DISTINCT e1_cd_aff FROM affair_raw_ref))
But for example this would only retrieve 6 between 5 and 9 but not 7 or 8.
Any suggestions
Thanks
Colin Larcombe