You need to select the last 5 entries among the sections

Question

I am stuck in demand. It may be simple, but I'm not worried.

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

The table is similar to

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 

Similarly, there are other tables in this. I need this for 5 tables.

Thank you for your help.

Regards, Amit

Answer 1

Depending 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

Hmmm, if I understand correctly, you can use row_number() with some date arithmetic:

 select ai.* from (select ai.*, row_number() over (partition by table_name, extract(year from business_date), extract(month from business_date) order by business_date desc ) as seqnum from audit_info ai where timestamp >= current timestamp - interval '5' month ) ai where seqnum = 1; 

Answer 3

If I understand correctly, you want to get the highest date per month for the last 5 months for which you have data. If so, select the group by year and month and use the max function to select the highest date in the 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