Retrieve sorted column value in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 12-Jun-16 21:13pm

Priyank_Mittal

Updated 12-Jun-16 22:33pm

Maciej Los

v2

Add a Solution

Comments

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.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-06-12T22:33:00

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:

Grp	GrpStartsAt	GrpEndsAt
100	101	105
109	115	117
110	119	120

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.

Retrieve sorted column value in SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Retrieve sorted column value in SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us