DENSE_RANK different sequences

  • Hi all,

    I have this data:

    Code                 register_sequence      registered_time      id

    4001_0_3       1                                      08:00:00                  1

    4001_0_3       2                                      08:02:17                   2

    4001_0_3       3                                      08:03:10                  3

    4001_0_3       4                                      08:04:08                 4

    4001_0_3       1                                      08:45:00                  5

    4001_0_3       2                                      08:47:17                   6

    4001_0_3       3                                      08:48:10                  7

    4001_0_3       4                                      08:49:08                 8

    4001_0_3       1                                      08:01:00                  9

    4001_0_3       2                                     08:03:17                  10

    4001_0_3       3                                     08:04:10                  11

    4001_0_3       4                                     08:05:08                 12

    What I need is to add a new field that identify the different sequences with a sequential number. Such as:

    Code                 register_sequence      registered_time     seq_number

    4001_0_3       1                                      08:00:00                1

    4001_0_3       2                                      08:02:17                1

    4001_0_3       3                                      08:03:10                1

    4001_0_3       4                                      08:04:08                1

    4001_0_3       1                                      08:45:00                2

    4001_0_3       2                                      08:47:17                 2

    4001_0_3       3                                      08:48:10                2

    4001_0_3       4                                      08:49:08                2

    4001_0_3       1                                      08:01:00                3

    4001_0_3       2                                     08:03:17                  3

    4001_0_3       3                                     08:04:10                 3

    4001_0_3       4                                     08:05:08                3

    Any idea ?

    • This topic was modified 1 month, 2 weeks ago by  vilaca.
    • This topic was modified 1 month, 2 weeks ago by  vilaca.
  • The answer is, yes... I have an idea but... you won't like it because the answer is, there is nothing in your original data to guarantee the sequence of even the original supposed sort of the data. Are there any other columns in the original rows of data that could be used to guarantee the sort of of the original data as you've presented it?

    EDIT... just so that people don't think that I've lost it, the ID column that is posted in the data above wasn't in the original post.  It was added afterwards.l 😀

     

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

  • What differentiates as Sequence ?

    Why is "4001_0_3       1                                      08:00:00  " newcol 1

    why is "4001_0_3       1                                      08:45:00" newcol 2

    why is "4001_0_3       1                                      08:01:00 " newcol 3

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi Jeff and Johan,

    I edited my original post, and included an id column, there's the only thing I can add to help sort the data.

    I have other fields, but none of them allow me to order correctly, nor to differenciate any sequence.

    PS: I know, my data sucks, but I can do nothing.. the database came to be like that, and now I'm stuck..

    • This reply was modified 1 month, 2 weeks ago by  vilaca.
    • This reply was modified 1 month, 2 weeks ago by  vilaca.
  • Please complete the following VALUES section code to give us your test data to work with:

    DROP TABLE IF EXISTS #SomeTab;

    CREATE TABLE #SomeTab
    (
    Id INT NOT NULL PRIMARY KEY CLUSTERED
    ,Code VARCHAR(10) NOT NULL
    ,register_sequence SMALLINT NOT NULL
    ,registered_time TIME NOT NULL
    );

    INSERT #SomeTab
    (
    Id
    ,Code
    ,register_sequence
    ,registered_time
    )
    VALUES
    (1, '4001_0_3', 1, '08:00:00')
    ,(2, '4001_0_3', 2, '08:02:17');

    SELECT * FROM #SomeTab st

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number

    • This reply was modified 1 month, 2 weeks ago by  Mark Cowne.
    • This reply was modified 1 month, 2 weeks ago by  Mark Cowne.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Thanks Mark Cowne.

    That is the perfect solution.. and right in front of my eyes.... still, didn't figure it out.

    DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number

    works beautifully!!

  • @vilaca ,

    Thanks for the ID column but that can still be problematic.  If it's created using an IDENTITY column or from a SEQUENCE object, there's no guarantee that you won't have gaps over time unless the column IS and IDENTITY column AND the data was loaded from a file in a single threaded fashion by something like BCP or BULK INSERT... or so people will tell you.  It's better to be safe than sorry. 

    The DENSE_RANK() method that Mark Cowne posted is certainly the right way to do this but... it needs a little bullet-proofing because of the gap problem I mentioned above.  We need to guarantee that something is guaranteed to have no gaps in an ever increasing fashion.  The only way to do that easily is to make one on the fly in the code, thusly...

       WITH cte AS
    (SELECT *, Contig = ROW_NUMBER() OVER (ORDER BY ID) --Contiguous, ever-increasing
    FROM #TestTable)
    SELECT *
    ,seq_number = DENSE_RANK() OVER(ORDER BY Contig - register_sequence)
    FROM cte
    ;

    Also, in the future, please submit your data in a readily consumable format.  You'll get answers much more quickly and they'll be tested.  Here's how I  made the example to test the above.  Please see the article at the first link in my signature line below for more info on the many advantages of taking that bit of extra time...

       DROP TABLE IF EXISTS #TestTable
    ;
    GO
    SELECT Code = CONVERT(CHAR(8),v.Code )
    ,register_sequence = CONVERT(INT ,v.register_sequence)
    ,registered_time = CONVERT(TIME ,v.registered_time )
    ,id = CONVERT(INT ,v.id )
    INTO #TestTable
    FROM (VALUES
    ('4001_0_3',1,'08:00:00', 1)
    ,('4001_0_3',2,'08:02:17', 2)
    ,('4001_0_3',3,'08:03:10', 3)
    ,('4001_0_3',4,'08:04:08', 4)
    ,('4001_0_3',5,'08:04:08', 5) --Added this
    ,('4001_0_3',1,'08:45:00', 6)
    ,('4001_0_3',2,'08:47:17', 8) --Skipped an ID (it happens)
    ,('4001_0_3',3,'08:48:10', 9)
    ,('4001_0_3',4,'08:49:08',10)
    ,('4001_0_3',1,'08:01:00',11)
    ,('4001_0_3',2,'08:03:17',12)
    ,('4001_0_3',3,'08:04:10',13)
    ,('4001_0_3',4,'08:05:08',14)
    )v(Code,register_sequence,registered_time,id)
    ;

    Here are the results from all that...

    Since the data already supports it, here's another way to get the correct output in a guaranteed fashion that doesn't look quite as obnoxious.

     SELECT *,seq_number = SUM(IIF(register_sequence=1,1,0)) OVER (ORDER BY ID)
    FROM #TestTable
    ORDER BY ID
    ;

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

  • vilaca wrote:

    Thanks Mark Cowne. That is the perfect solution.. and right in front of my eyes.... still, didn't figure it out.

    DENSE_RANK() OVER(ORDER BY Id - register_sequence) AS seq_number

    works beautifully!!

    Careful now.  There's no guarantee that the ID will always be perfectly contiguous.  See my post above.

    This is what happens with Mark's otherwise perfect code  if you have either a gap ...

    • This reply was modified 1 month, 2 weeks ago by  Jeff Moden.

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

  • This was removed by the editor as SPAM

Viewing 10 posts - 1 through 10 (of 10 total)

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