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.
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.