Getting one entry per RANK


Getting one entry per RANK

I need to get the most recent records (based on date) for each ID. I cannot do a group by since there are too many non-aggregate values that I do not want to consider when grouping. I have tried:
rank() over partition by ID,modification date order by modification date (DESC)

However, when the dates are the same for more than one record for a specific ID I get the same rank. I only need to pull one record per id and date when rank =1.


Senior Apprentice

Re: Getting one entry per RANK

Hi Carole,
just add more columns to the Order By to make it unique or use row_number instead of rank...


Re: Getting one entry per RANK


I know this is an old post, but for those looking at this, try doing something like this.

select id_field, date_field, id2_field
from table1
where ...
qualify rank() over (partition by id_field order by id_field, date_field desc, id2_field desc) = 1

This will get the id_field for the max (rank = 1) date_field and exclude everything else where the rank isn't = 1 and the id2_field is a tie-breaker. If you don't have an id2_field, then you probably won't need a tie-breaker and you can simply exclude it from your query.