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.
Rank = 1
SEQ = 5
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
You would use:
rank() over (partition by part order by to_number(seq) desc) as rank
That is, remove seq from the partition by.
seq
partition by
rank() gives ties the same value, so multiple rows will have 1.
rank()
1
1.4m articles
1.4m replys
5 comments
56.9k users