CODEWITHSUNDEEP
sqlsql-server
Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

SQL Query for Select Sequence Numbers

SQL Query for Select Sequence Numbers

In SQL server, I want to select rows based on sequence numbers. For example I am having data as below:

ID RowNos
A  1
B  2
X  NULL
C  4
D  5
Y  NULL
E  7
F  8
G  9
H  11
I  13

Query Should return 

ID NextID 
A  B      -- Since RowNos 1,2 is in sequence
C  D      -- Since RowNos 4,5 is in sequence
E  G      -- Since RowNos 7,8,9 is in sequence

I don't have idea to start this query. Otherwise I'll post my trial too.

Upvotes: 2

Views: 542

Answers (2)

Rui Couto
Rui Couto

Reputation: 26

to select them ordered should be something like:

SELECT * FROM table_name WHERE RowNos IS NOT NULL ORDER BY RowNos ASC;

Upvotes: 0

Devart
Devart

Reputation: 121912

DECLARE @t TABLE (ID CHAR(1), RowNos INT)
INSERT INTO @t
VALUES
    ('A', 1), ('B', 2), ('X', NULL),
    ('C', 4), ('D', 5), ('Y', NULL),
    ('E', 7), ('F', 8), ('G', 9),
    ('H', 11), ('I', 13)

SELECT MIN(ID), MAX(ID)
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY RowNos)
    FROM @t
) t
WHERE RowNos IS NOT NULL
GROUP BY RowNos - rn
HAVING MIN(ID) != MAX(ID)

Output:

---- ----
A    B
C    D
E    G

Upvotes: 3

Related Questions