CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Sarah Mwamlima
Sarah Mwamlima

Reputation: 61

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 transaction. I just want to pick the balance at the maximum date of every month.Any help??

Below is a snip of My dataset.

enter image description here

Upvotes: 0

Views: 566

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

You can do it also without a sub-query:

WITH b(ID, "date",bal) AS
  (
   SELECT 'CUST_I',DATE '2013-07-27', 14777.44 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-07-26', 71085.13 FROM dual UNION ALL
   SELECT 'CUST_I',DATE '2013-08-27', 66431.35656 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-08-26', 63102.68622 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-08-20', 6310.68622 FROM dual UNION ALL
   SELECT 'CUST_H',DATE '2013-08-10', 630.68622 FROM dual UNION ALL
   SELECT 'CUST_G',DATE '2013-09-25', 89732.04889 FROM dual UNION ALL
   SELECT 'CUST_E',DATE '2013-09-23', 83074.70822 FROM dual 
  )
SELECT ID,  
    MAX("date") KEEP (DENSE_RANK FIRST ORDER BY "date" desc) AS MAX_DATE,
    MAX(bal) KEEP (DENSE_RANK FIRST ORDER BY "date" desc) AS MAX_BAL
FROM b
GROUP BY 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|
+-----------------------------+

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

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
   group by to_char("date",'yyyymm')  
) c2 on ( c1."date" = c2.max_date );

SQL Fiddle Demo

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

You can try using window function - row_number()

select * from
(
SELECT *,row_number() over(partition by extract(YEAR FROM Date), extract(MONTH FROM Date) order by date desc) as rn
FROM t
)rn=1

Upvotes: 1

Related Questions