Gaps in sequences
My table has one column of type integer. The data within that column is a simple sequence of numbers. If I have a number of rows with the values incrementing by one, what SQL could I use to determine if there is a skip in sequence?
For example, in my one column, I have 999 rows with the integer values of 1 to 1000, excluding 500. How can I return an SQL result that has one row with the value of 500?
The easiest way is to join the table to itself. Join each row to the one with the next id, by matching t1.id + 1 = t2.id. Use an outer join, and look for unmatched rows, i.e. where t2.id is null:
select t1.id + 1 as missing from onethousand t1 left outer join onethousand t2 on t1.id + 1 = t2.id where t2.id is null missing 500 1001
That's the result you wanted, except for the 1001, which is from the row with the highest id in the table. In other words, 500 is missing from within the sequence, and 1001 is the first number missing after the sequence. You could remove this with:
and t1.id < (select max(id) from onethousand)
The t1.id + 1 = t2.id self-join method will actually only report the first number in a gap in the sequence. If you were to delete the 501 and 502 rows, the outer join query will still only report 500 as missing. When matching t1.id + 1 = t2.id, 500 matches 499, but 501, 502, and 503 never come up because 500, 501, and 502 are missing from the t1 side of the join. This is easily seen with this query:
select t1.id as t1id , t1.id + 1 as t1idplus1 , t2.id as t2id from onethousand t1 left outer join onethousand t2 on t1.id +1 = t2.id where t1.id between 497 and 504 order by t1.id t1id t1idplus1 t2id 497 498 498 498 499 499 499 500 NULL 503 504 504 504 505 505
If you needed to itemize all the missing numbers, you could do that with a left outer join from The integers table.