CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Cannon
Cannon

Reputation: 319

Return most recent row in Toad

So I have a dataframe that looks like this in Toad:

ID    Total_Amt   date1        date2      Name1   Name2
1     16          11/1/12      6/30/12    BOB     JON
1     17          11/1/13      7/12/13    BILL    JACK
1     17          11/1/13      7/1/13     BEN     ADA
1     18          11/1/14      11/5/14    JEFF    ALAN
1     18          11/1/14      11/1/14    JERI    FRANK

My goal is to just return rows that are the most recent for each date1. since I only have one row for 11/1/12 that would be my most recent. however for 11/1/13 in date1 there are two rows with the same date. So I then want to look at date2 and say okay well my most recent date for this is 7/12/13 so I only want to return that row and not both.

My resulting data frame would be as follows:

 ID    Total_Amt   date1        date2      Name1   Name2
1     16          11/1/12      6/30/12    BOB     JON
1     17          11/1/13      7/12/13    BILL    JACK
1     18          11/1/14      11/5/14    JEFF    ALAN

I am only concerned with the most recent row as it pertains to each date so if there is more than one date in date1 with the same date then I want it to only pull in the most recent by also using date2 and looking at what is the most recent from that field for the date found in date1.

Upvotes: 0

Views: 554

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use row_number() window function

select ID ,Total_Amt,date1,date2,Name1,Name2 from    
(
 select ID ,Total_Amt,date1,date2,Name1,Name2,
 row_number() over(order by date1 desc,date2 desc) rn from table_name
) t where rn=1

Upvotes: 1

Related Questions