Teradata Forums / Analytical Applications / Teradata / Getting one entry per RANK / Latest Posts

RE: Getting one entry per RANK

tajulrich — Wed, 09 Apr 2008 15:56:37 GMT

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_fieldfrom table1where ... qualify rank() over (partition by id_field order by id_field, date_field desc, id2_field desc) = 1This 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

RE: Getting one entry per RANK

dnoeth — Sat, 19 Aug 2006 13:26:26 GMT

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

Dieter

Getting one entry per RANK

carole — Fri, 18 Aug 2006 11:44:10 GMT

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