how to check sequence in sql server

Question

i have some scenarios and i need check check sequence for that , i need sequence for following values

This is the actual data which is presented in sql db

10001 10001
10002 10002
10003 10003
10005 10005
10006 10006
10008 10008
10009 10009
10010 10010

so i need a range for above values like this

10001 10003
10005 10006
10008 10010

and if i am adding new row in sql

10004 10004

after insert above row, actual data which is presented in sql db

10001 10001
10002 10002
10003 10003
10004 10004
10005 10005
10006 10006
10008 10008
10009 10009
10010 10010

then the range will be

10001 10006
10008 10010

and if i am adding new row in sql

10007 10007

after insert above row, actual data which is presented in sql db

10001 10001
10002 10002
10003 10003
10004 10004
10005 10005
10006 10006
10008 10008
10007 10007
10009 10009
10010 10010

then the range will be

10001 10010

can anyone please help to solve this issue

Tim Biegeleisen · Accepted Answer · 2016-07-15 05:06:05Z

This is a classic gaps and islands problem. I was able to immediately recognize what your query would require, but as these sorts of problems seem to appear fairly infrequently on Stack Overflow, I needed a reference to jolt my memory. There is a great article on Microsoft's TechNet site which goes into depth on gaps and islands and performance. I recommend that you read it so you can understand how the query below is working.

SELECT t1.gapID as startOfGroup, MIN(t2.gapID) AS endOfGroup
FROM
(
    SELECT gapID
    FROM gaps tbl1 
    WHERE NOT EXISTS
    (
        SELECT * FROM gaps tbl2 
        WHERE tbl1.gapID = tbl2.gapID + 1
    )
) t1
INNER JOIN
(
    SELECT gapID
    FROM gaps tbl1 
    WHERE NOT EXISTS
    (
        SELECT * FROM gaps tbl2 
        WHERE tbl2.gapID = tbl1.gapID + 1
    )
) t2
    ON t1.gapID <= t2.gapID
GROUP BY t1.gapID

I created a Fiddle for your problem in MySQL, because SQL Server seems to be perpetually busted on that site.

SQLFiddle

dean · Accepted Answer · 2016-07-15 06:46:59Z

1

With any newer version of SQL Server, this can be easily solved using window functions:

declare @t table(i int)
insert @t values (10001), (10002), (10003), (10005), (10006), (10008), (10009), (10010)

;with x as (
  select *, case when i = lag(i) over(order by i)+1 then 0 else 1 end g from @t
),
y as (
  select i, sum(g) over(order by i) sg from x
)
select min(i), max(i) from y group by sg

answered Jul 15, 2016 at 6:46

dean

10.1k2 gold badges27 silver badges27 bronze badges

Collectives™ on Stack Overflow

how to check sequence in sql server

2 Answers 2

SQLFiddle

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related