SQL to detect "sequence break"

Question

I have data in which incremental sequence is broken somewhere, maybe multiple times. E.g. (2,3,4,5,6,8,10).

I want to get:

1. the first "broken" place (6 is the last good in the example)
2. the number of "broken" places (2 times, on 7 and 9)

using SQL (preferably general, which works on oracle and mysql and other sql platforms).

Using sequences or auto_increment are platform-specific.

I tried self-join constructions like

select curr.id+1 as first_fail from junk as prev
join junk as curr 
on (prev.id+1 = curr.id) 
order by curr.id desc limit 1;

(http://sqlfiddle.com/#!9/bae781/4/0) but it seems ugly, and can't get the number of "broken" places this way.

Answer 1

All beginning "breaks" sorted by ids:

select j1.id + 1 as id
from junk j1
left join junk j2 on j2.id = j1.id + 1
where j2.id is null
    and j1.id <> (select max(id) from junk)
order by j1.id;

Pick the first row to get the first "break". Count number of rows to get the number of "breaks".

If you need the number of all missing ids:

-- get number of missing ids
select 
    -- num rows you should have
    (select max(id) from junk) - (select min(id) from junk)     + 1
    -- num rows you really have
    - count(*) as num_missings
from junk;

Or shorter:

select max(id) - min(id) + 1 - count(*) as num_missings from junk;