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.

So my desired output would be:

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

CodePudding user response：

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

Fiddle

CodePudding user response：

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;