How to select max date from table for distinct values-CodePudding

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.

CodePudding user response：

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