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