Post reply

Sequential numbers

  • 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?

  • April 5, 2014 at 10:40 am

    #1703502

    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://qa.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)
    Intro to Tally Tables and Functions

  • April 7, 2014 at 12:37 am

    #1703658

    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

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

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