Quick and dirty way of finding gaps in sequential rows (using sqlite as an example).md

Quick and dirty way of finding gaps in sequential rows (using sqlite as an example)

Imagine you have a table that contains data with a monotonically increasing key. You can write some quick SQL code (I'll use sqlite3) to tell whether you have any gaps in the sequence of ids.

create table sequential(id int not null, name varchar(10) null);

delete from sequential;
insert into sequential(id, name) select 1, "one";
insert into sequential(id, name) select 2, "two";
insert into sequential(id, name) select 4, "four";
insert into sequential(id, name) select 5, "five";
insert into sequential(id, name) select 9, "nine";

select * from sequential

id|name
1|one
2|two
4|four
5|five
9|nine

This query will give you rows where there is a gap in the next value (will always return the first row)

select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null;

id|name
1|one
4|four
9|nine

This query will give you rows where there is a gap in the previous row (will always return the last row)

sqlite> select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null;

id|name
2|two
5|five
9|nine

unioning the 2 queries should only ever return the first and last row if there are no gaps

 select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null
 union
 select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null;

id|name
1|one
2|two
4|four
5|five
9|nine

Now lets insert sequential data and validate our union query only returns 2 rows, the first and the last (you could refine the query and exclude the min and max rows)

delete from sequential;
insert into sequential(id, name) select 1, "one";
insert into sequential(id, name) select 2, "two";
insert into sequential(id, name) select 3, "three";
insert into sequential(id, name) select 4, "four";
insert into sequential(id, name) select 5, "five";
insert into sequential(id, name) select 6, "six";
insert into sequential(id, name) select 7, "seven";
insert into sequential(id, name) select 8, "eight";
insert into sequential(id, name) select 9, "nine";

select * from sequential;
id|name
1|one
2|two
3|three
4|four
5|five
6|six
7|seven
8|eight
9|nine

 select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null
 union
 select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null;

id|name
1|one    <-- first row
9|nine   <-- last row.

Final

select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null and a.id <> (select min(id) from sequential)
union
select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null and a.id <> (select max(id) from sequential);