Fetch A Record On Maximum Date Of Every Month

I want to fetch customers balances at the maximum date of every month, in every year in database. The Balance table has balances at the end of everyday when customer does transacti

Solution 1:

You can try using window function - row_number()

select*from
(
SELECT*,row_number() over(partitionbyextract(YEARFROMDate), extract(MONTHFROMDate) orderbydatedesc) as rn
FROM t
)rn=1

Solution 2:

You can do it also without a sub-query:

WITH b(ID, "date",bal) AS
  (
   SELECT'CUST_I',DATE'2013-07-27', 14777.44FROM dual UNIONALLSELECT'CUST_H',DATE'2013-07-26', 71085.13FROM dual UNIONALLSELECT'CUST_I',DATE'2013-08-27', 66431.35656FROM dual UNIONALLSELECT'CUST_H',DATE'2013-08-26', 63102.68622FROM dual UNIONALLSELECT'CUST_H',DATE'2013-08-20', 6310.68622FROM dual UNIONALLSELECT'CUST_H',DATE'2013-08-10', 630.68622FROM dual UNIONALLSELECT'CUST_G',DATE'2013-09-25', 89732.04889FROM dual UNIONALLSELECT'CUST_E',DATE'2013-09-23', 83074.70822FROM dual 
  )
SELECT ID,  
    MAX("date") KEEP (DENSE_RANK FIRSTORDERBY "date" desc) AS MAX_DATE,
    MAX(bal) KEEP (DENSE_RANK FIRSTORDERBY "date" desc) AS MAX_BAL
FROM b
GROUPBY ID, TRUNC("date", 'MM');

+-----------------------------+|ID    |MAX_DATE  |MAX_BAL    |+-----------------------------+|CUST_E|23.09.2013|83074.70822||CUST_G|25.09.2013|89732.04889||CUST_H|26.07.2013|71085.13||CUST_H|26.08.2013|63102.68622||CUST_I|27.07.2013|14777.44||CUST_I|27.08.2013|66431.35656|+-----------------------------+

Solution 3:

You may use a self join for your table call cust_balances :

select c1.*
  from cust_balances c1
join 
(  
  select max("date") max_date
    from cust_balances
   groupby to_char("date",'yyyymm')  
) c2 on ( c1."date" = c2.max_date );

SQL Fiddle Demo