# How to identify when there is a break in continuity of an integer on a column

• ### Question

• Gurus,

I have two columns, one column has a document ID and a given document can have many pages. The second column has the pages. Now I want to find out when the page number is broken. For example, if doc ID 1 has 3 rows and each of the three has 1,2,3 and then the fourth row has document 1 but the value jumps from 3 to 7 and then goes to 8,9,10 and then jumps again and starts from 17, i want to have the ranges identified.

DocID Page Number

1    1

1 2

1 3

1 7

1 8

1 9

1 10

1 17

1 18

19

1    20

The result should look like

DocID Page Number

1   1-3

1     7-10

1   17-20

ebro

Thursday, July 16, 2015 8:01 PM

• Create table test (DocID int, PageNumber int)

Insert into test values (1,1),(1,2),(1,3),(1,7),
(1,8),(1,9),(1,10),(1,17),
(1,18),(1,19),(1,20)

;with mycte as (
Select * , PageNumber- row_number() Over(Order by PageNumber) grp from test)

Select DocID, Min(PageNumber),  Max(PageNumber) from mycte
Group by DocID,grp

drop table test
• Marked as answer by Monday, July 20, 2015 5:47 AM
Thursday, July 16, 2015 8:07 PM
• This is very common problem and the name of it 'Gaps and Islands', you can google by it.

Refactoring Ranges

I also did a quick search by Itzik Ben-Gan and this problem and found

http://www.manning.com/nielsen/SampleChapter5.pdf

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

• Marked as answer by Thursday, July 16, 2015 8:59 PM
Thursday, July 16, 2015 8:09 PM

### All replies

• Create table test (DocID int, PageNumber int)

Insert into test values (1,1),(1,2),(1,3),(1,7),
(1,8),(1,9),(1,10),(1,17),
(1,18),(1,19),(1,20)

;with mycte as (
Select * , PageNumber- row_number() Over(Order by PageNumber) grp from test)

Select DocID, Min(PageNumber),  Max(PageNumber) from mycte
Group by DocID,grp

drop table test
• Marked as answer by Monday, July 20, 2015 5:47 AM
Thursday, July 16, 2015 8:07 PM
• This is very common problem and the name of it 'Gaps and Islands', you can google by it.

Refactoring Ranges

I also did a quick search by Itzik Ben-Gan and this problem and found

http://www.manning.com/nielsen/SampleChapter5.pdf

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

• Marked as answer by Thursday, July 16, 2015 8:59 PM
Thursday, July 16, 2015 8:09 PM
• Thank you very much!!