|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/4/2006 6:08:00 PM
Posts: 1,
Visits: 1
|
|
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.
Thanks
Carole
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 7:26:06 AM
Posts: 474,
Visits: 202
|
|
Hi Carole,
just add more columns to the Order By to make it unique or use row_number instead of rank...
Dieter
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 4/9/2008 4:16:39 PM
Posts: 1,
Visits: 5
|
|
Carole,
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.
Enjoy!
tau
|
|
|
|