Post reply

Row_Number issue....

  • February 14, 2024 at 8:15 pm

    #4378771

    Hi

     

    I know I'm doing something wrong in my row_number logic, just not sure what..

    My data looks like this

    coid       , caseoid     , date                                          , RN

    969284, 24066903, 2024-02-06 00:00:00.000, 1

    969284, 24066903, 2024-02-06 00:00:00.000, 1

    969284, 23850391 ,2024-01-10 00:00:00.000 ,1

    969284, 23496956 ,2023-12-21 00:00:00.000 ,1

    969284, 23496956 ,2023-12-21 00:00:00.000, 2

    969284, 23445195 ,2023-11-16 00:00:00.000 ,1

    969284, 22763459, 2023-08-19 00:00:00.000, 1

    969284, 22491420, 2023-07-14 00:00:00.000, 1

    23336082, 23336086, 2023-11-02 00:00:00.000, 1

    2333,6082, 23336086, 2023-11-02 00:00:00.000, 2

    23336082 ,23336086 ,2023-11-02 00:00:00.000 ,3

    My row_number logic is

    ,ROW_NUMBER() OVER(PARTITION BY coid, caseoid ORDER BY [admission date] desc) as RN

    My desired output would be theat RN would mark the most recent caseoid  partioned by the coid, with a '1' and others of the same caseoid would be a '1

    so in my example

    969284, 24066903, 2024-02-06 00:00:00.000, RN=1

    969284, 24066903, 2024-02-06 00:00:00.000, RN=1

    969284, 23850391 ,2024-01-10 00:00:00.000 ,  RN=2

    969284, 23496956 ,2023-12-21 00:00:00.000 , RN=3  etc  since caseoids are different  except the first two where the have the same caseoid

    but for  coid of  23336082 since the caseoid are all the same I would want

    23336082, 23336086, 2023-11-02 00:00:00.000, RN=1

    2333,6082, 23336086, 2023-11-02 00:00:00.000, RN=1

    23336082 ,23336086 ,2023-11-02 00:00:00.000 , RN=1

     

    Thanks in Advance

     

     

     

     

     

     

     

  • February 14, 2024 at 8:47 pm

    #4378783

    Typo

  • February 15, 2024 at 2:04 am

    #4378889

    It's not your first visit here so you should already know that, if you'd posted the data in a readily consumable format, you'd have a code-tested answer by now. 😉

    I believe the function you're looking for is DENSE_RANK().

     

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

  • February 15, 2024 at 4:56 pm

    #4379260

    Hi JEff,

     

    Thanks for getting back. I have posted before, but honestly , not sure how to post the data in consumable format. Any how to links?

     

    Thanks

     

  • February 15, 2024 at 5:46 pm

    #4379278

    Duplicate due to slow response/double click

  • February 15, 2024 at 5:47 pm

    #4379279

    In the signature of Jeff's reply:

    How to post code problems

Viewing 6 posts - 1 through 5 (of 5 total)

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