Need to pick latest 5 records among the partitions

Question

I am stuck in a requirement. It might be simple but i am not getting through.

I have one audit table Audit_Info which captures the audit information of all tables. A table could be run multiple times on the same business date. My requirement is to get the latest business date record for each month upto last 5 months. It may happen that for one particular month the table was not run.

Table is like

table_name business_date src_rec tgt_rec del_rec load_timestamp
abc          25/10/2015   10      10      0       23/01/2016 03:06:56
abc          25/10/2015   10      10      0       23/01/2016 05:23:34
abc          07/09/2015   10      10      0       23/10/2015 05:37:30
abc          05/08/2015   10      10      0       23/09/2015 05:23:34
abc          15/06/2015   10      10      0       23/07/2015 05:23:34
abc          25/04/2015   10      10      0       23/05/2015 05:23:34

similary there are other tables in this. I need it for 5 tables.

Thanks for your help.

Regards, Amit

Answer 1

Based on your expected result this should be close:

select * from tab
where  -- last five months
   business_date >= add_months(trunc(current_date),-5)
qualify
   row_number()  
   over (partition by trunc(business_date)  -- every month
         order by business_date desc, load_timestamp desc) -- latest date

Answer 2

If I understand correctly, you want the greatest date per month for the 5 most recent months that you have data for. If so, group by year and month and use the max function to select the greatest date per month:

select top 5 
    max(business_date), extract(year from business_date) , extract(month from business_date)
from mytable
group by extract(year from business_date), extract(month from business_date)
order by extract(year from business_date) desc, extract(month from business_date) desc