Sequential numbers

Question

Sequential numbers

rui_leote

SSC Veteran

Points: 201
More actions
April 5, 2014 at 9:56 am

#287316

Hi, I have a problem. In my databse I have the following numbers available:
101
104
105
110
111
112
113
114
What I need is to get a select query with records and sequentials numbers after it like:
101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0
How can I do It?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

This is a duplicate post. Please don't post any answers here because it'll just separate potential solutions from what is already in progress. Instead, please the in-progress duplicate post at the follow URL. Thanks folks.

http://www.sqlservercentral.com/Forums/Topic1558709-8-1.aspx

And, to be clear, the OP is actually using 2008 and above even though he posted to a 7, 2000 forum.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kumariannapureddy SSC Journeyman Points: 89 More actions · Answer 2

DECLARE @Seq TABLE(Id INT,SID INT)

INSERT INTO @Seq VALUES(1,110),(2,104),(2,105),(3,110),(3,111),(3,112),(3,113),(3,114),(4,115),(4,116)

SELECT ID,Sid,

STUFF((SELECT ','+CAST(SId AS VARCHAR(10)) FROM @Seq AS SS

WHERE SS.SId > S.SId AND SS.Id = S.Id

ORDER BY SId DESC FOR XML PATH('')),1,1,'') AS 'Colimn'

FROM @Seq AS S