Row_Number issue....

Question

Post reply

Row_Number issue....

jbalbo

SSCertifiable

Points: 7583
More actions
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

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

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

ratbak Default port Points: 1405 More actions · Answer 1

ratbak

Default port

Points: 1405

February 14, 2024 at 8:47 pm

#4378783

Typo

Jeff Moden SSC Guru Points: 1004581 More actions · Answer 2

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)