SQL Query for Select Sequence Numbers

Question

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.

Your example seems somewhat unclear to me. Are you looking at sequence of ID or RowNos - if the latter is it guaranteed that they are unique and will be sequential except for the intervening NULL? What version of SQL Server? — Martin Smith
– Martin Smith, Commented Jul 18, 2016 at 10:49
@MartinSmith, I want to retrieve the ID and Next ID based on the sequence. For example, 1,2 is the sequence because 3 is missing So A(1) Is the ID and B(2) is the Next ID. — Vignesh Kumar A
– Vignesh Kumar A, Commented Jul 18, 2016 at 10:53
Is RowNos unique for not null values? What version of SQL Server? — Martin Smith
– Martin Smith, Commented Jul 18, 2016 at 10:57

Devart · Accepted Answer · 2016-07-18 10:45:43Z

3

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

answered Jul 18, 2016 at 10:45

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rui Couto · Accepted Answer · 2016-07-18 10:49:15Z

0

to select them ordered should be something like:

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

answered Jul 18, 2016 at 10:49

Rui Couto

264 bronze badges

Collectives™ on Stack Overflow

SQL Query for Select Sequence Numbers

2 Answers 2

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