How to select max date from table for distinct values

Question

I have a table that looks like this:

date        account asset    amount
01-01-2022  1       A        12     
01-01-2022  1       B        100
02-01-2022  1       A        14
02-01-2022  1       B        98
01-01-2022  2       A        15     
01-01-2022  2       C        230
02-01-2022  2       A        13
02-01-2022  2       B        223
03-01-2022  2       A        17
03-01-2022  2       B        237

I want to be able to get the last values (i.e. max date) for each account. So the result should look like this:

date        account asset    amount
02-01-2022  1       A        14
02-01-2022  1       B        98
03-01-2022  2       A        17
03-01-2022  2       B        237

How can this be done in SQL? EDIT: Notice that the max dates for the different accounts are not the same.

`select * from table where date=(select max(date) from table) order by account,asset` — Nathan_Sav, Mar 31 '22 at 16:02
Sorry if my example is a little misleading. Not all accounts have the same date. I will edit my question. Thanks — MathMan 99, Mar 31 '22 at 16:14

score 2 · Accepted Answer · answered Mar 31 '22 at 16:51

You can do it by first selecting the max dates for each account and then forcing the match between accounts given the date constraints, like in the following query:

SELECT 
    *
FROM 
    (
    SELECT 
        MAX(date) AS date, 
        account
    FROM 
        tab 
    GROUP BY 
        account
    ) max_date_per_account
INNER JOIN 
    tab
ON 
    tab.date = max_date_per_account.date 
AND  
    tab.account = max_date_per_account.account

How to select max date from table for distinct values

1 Answers1