use tempdb
if( OBJECT_ID('tempdb..test') is not null)
drop table test
create table test
( id int)
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
--insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
--insert into test values (18)
insert into test values (18)
insert into test values (19)
--go
select * from test
--Now I want to list the result of the non-contignous row as below,how can I do it?
--Missing after Missing before
--------------- --------------
--6 8
--9 11
--...
-- Gaps solution 1 subquery using exist clause
select a.id as 'from',
(select MIN(id) from test c where c.id>a.id) as 'to'
from (select distinct ID from test) a where not exists (select * from test b where a.id+1=b.id
)
and a.id< (select MAX(id) from test )
-- Gaps solution 2 : slower ,using pairs
select cur ,[next]
from
(
select a.id 'cur',
(select MIN(Id) from test b where b.id>a.id)
'next'
from test a
) c
where c.cur +1< c.[next]
-- Gaps solution 3: row_number
go
with c as
(
select ID , ROW_NUMBER () over ( order by id) as rowNum from test
)
select cur.ID,cur.rowNum ,nex.id from c as cur join c as nex
on cur.rowNum+1=nex.rowNum
where cur.id+1<nex.id
--islands .. solution 1 row_number
go
with starts
as
(
select id, ROW_NUMBER() over(order by id ) rn from test a
where not exists
(
select * from test b where a.id-1=b.id
)
)
,
ends
as
(
select id,ROW_NUMBER() over (order by id) rn from test a
where not exists
(
select * from test b where a.id+1=b.id
)
)
select a.id [from] ,b.id [to] from starts a join ends b on a.rn=b.rn
