15,969,567 members
See more:
What i have:
I have a Column

```ID  SerialNo
1    101
2    102
3    103
4    104
5    105
6    116
7    117
8    115
9    119
10   120```

These are just the 10 dummy rows actual rows in table are in lakhs.

What i Want to get:

A method or formula like any sorting technique which could return me the starting and ending element of [SerialNo] Column for every sub-series. For example,

Expected Result: 101-105, 115-120

The comma separation in the above result is not important, only the starting and ending elements are important.

What I have tried:

I did it by PL/SQL programming, by running a loop in which i'm getting the starting and ending elements getting stored in a TABLE.

But due to very huge no. of rows(in lakhs) the query execution is taking very long(around 2 minuts).

I have also searched about some sorting techniques for the SQL Server but i found nothing. Because rendering every row will take twice the time then a sorting algorithm

Posted
Updated 12-Jun-16 22:33pm
v2
Mehdi Gholam 13-Jun-16 3:16am
Why do you want to do this?
Priyank_Mittal 13-Jun-16 3:31am
To get a series of items. Purpose is to show it into the report at the user's end.
CHill60 13-Jun-16 4:27am
How are you identifying what is a sub-series?
Priyank_Mittal 13-Jun-16 4:29am
Sub series are the small small series of elements, range for Sub series could be any for the sub-series.... in hundreds, in thousands.

## Solution 1

What you're looking for is called: gaps and islands issue.
See:
The SQL of Gaps and Islands in Sequences[^]
Islands and Gaps in Sequential Numbers[^]

[EDIT]
Test it:
SQL
```DECLARE @tmp TABLE(ID INT IDENTITY(1,1), SerialNo INT)

INSERT INTO @tmp (SerialNo)
VALUES(101),(102),(103),(104),(105),
(116),(117),(115),(119),(120)

SELECT  E.SerialNo- E.RowNo AS Grp, MIN(E.SerialNo) AS GrpStartsAt , MAX(E.SerialNo) AS GrpEndsAt
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY SerialNo) AS RowNo, SerialNo
FROM @tmp
) AS E
GROUP BY E.SerialNo- E.RowNo```

Result:
GrpGrpStartsAtGrpEndsAt
100101105
109115117
110119120

For such small portion of data, it should work like a charm.
The basic idea is to return `SerialNo` sorted in ascending order, then to use `RowNo` together with `SerialNo` to create `Grp`.

v3