How to get most recent balance for every user and its corresponding dates

Zoey Malkov 0
Viewed 32

I have a table called balances. I want to get the most recent balance for each user, forever every financial year and its corresponding date it was updated.

name balance financial_year date_updated
Bob 20 2021 2021-04-03
Bob 58 2019 2019-11-13
Bob 43 2019 2022-01-24
Bob -4 2019 2019-12-04
James 92 2021 2021-09-11
James 86 2021 2021-08-18
James 33 2019 2019-03-24
James 46 2019 2019-02-12
James 59 2019 2019-08-12

So my desired output would be:

name balance financial_year date_updated
Bob 20 2021 2021-04-03
Bob 43 2019 2022-01-24
James 92 2021 2021-09-11
James 59 2019 2019-08-12

I've attempted this but found that using max() sometimes does not work since I use it across multiple columns

SELECT name, max(balance), financial_year, max(date_updated)
FROM balances
group by name, financial_year
oracle11g max oracle database sql
2 Answers
select NAME 
      ,BALANCE  
      ,FINANCIAL_YEAR   
      ,DATE_UPDATED
from  (
       select t.*
             ,row_number() over(partition by name, financial_year order by date_updated desc) as rn
       from   t
       ) t
where  rn = 1
NAME BALANCE FINANCIAL_YEAR DATE_UPDATED
Bob 43 2019 24-JAN-22
Bob 20 2021 03-APR-21
James 59 2019 12-AUG-19
James 92 2021 11-SEP-21

Fiddle

DannySlor0

The problem is not that you use max() across multiple columns but the fact, that max() returns the maximum value. In your example, the highest balance of Bob in financial year 2019 was 58. The 'highest' (last) date_updated was 2022-01-24, but at this time the balance was 43.

What you're looking for is the balance at the time the balance was updated last within a financial year per user, that is something like

SELECT b.name, b.financial_year, b.balance, b.date_updated
  FROM balances b
  INNER JOIN (SELECT name, financial_year, max(date_updated) last_updated
                FROM balances GROUP BY name, financial_year) u
          ON b.name = u.name AND b.financial_year = u.financial_year AND b.date_updated = u.last_updated;
Mihe0
Related