Row_Number issue....

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  February 14, 2024 at 8:16 pm

  #4378772

  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

   

   

   

   

   

   

   

• ratbak

  Ten Centuries

  Points: 1358

  More actions

  February 14, 2024 at 8:46 pm

  #4378781

  Duplicate of https://www.sqlservercentral.com/forums/topic/row_number-issue