Wednesday, August 25, 2010

How to get missing numbers of a given sequence of numbers. Eg: 1,2,4,5,7,9,11,12 Output Should be : 3,6,8,10

create table Sequence_Numbers (
id int not null primary key
);

insert into Sequence_Numbers(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20);


select * from Sequence_Numbers

select l.id + 1 as start
from Sequence_Numbers as l
left outer join Sequence_Numbers as r on l.id + 1 = r.id
where r.id is null;