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?
Requires Free Membership to View
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.
This was first published in February 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation