CODEWITHSUNDEEP
sqloracle-database
Nick
Nick

Reputation: 13

Latest record by Rank

I am trying to pick the latest record which appears twice. I used the Rank to identify the latest by making Rank = 1. But I need both the latest SEQ but Rank shows 1 and 2. How do I make both the Ranks 1 for SEQ = 5 so I could pick both? Below is the code I tried.

row_number() over (partition by PART,SEQ order by TO_NUMBER(SEQ) desc) as Rank

SEQ            PART        RANK  

5               P1          1

5               P1          2

4               P1          1

4               P1          2

Upvotes: 0

Views: 353

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You would use:

rank() over (partition by part order by to_number(seq) desc) as rank

That is, remove seq from the partition by.

rank() gives ties the same value, so multiple rows will have 1.

Upvotes: 2

Related Questions