SQL Filter Each Record By The Latest Date-CodePudding

I have two tables I am joining.

asset
assetid  length
1          10
2          190
3          8


inspection
flag     date     assetid
0       2/3/2021    3
1       5/4/2022    3
1       3/19/2021   1
1       6/14/2020   2

When I join I get this

SELECT
inspection.flag,
inspection.date,
asset.assetid,
asset.length
FROM asset left join inspection on asset.assetid = inspection.assetid
GROUP BY inspection.flag, inspection.date, asset.assetid, asset.length


Flag     Date     AssetID  Length
0       2/3/2021    3        8
1       5/4/2022    3        8
1       3/19/2021   1       10
1       6/14/2020   2       190

But I want to filter by each record that has the latest date so i can find out what the most recent flag is. Like below

Flag     Date     AssetID  Length
1       5/4/2022    3        8
1       3/19/2021   1       10
1       6/14/2020   2       190

All the methods i have been using gives me the latest single record of all that has the latest date. Any Idea?

CodePudding user response：

One option uses ROW_NUMBER:

WITH cte AS (
    SELECT i.flag, i.date, a.assetid, a.length,
           ROW_NUMBER() OVER (PARTITION BY a.assetid ORDER BY i.date DESC) rn
    FROM asset a
    INNER JOIN inspection i ON a.assetid = i.assetid
)

SELECT flag, date, assetid, length
FROM cte
WHERE rn = 1
ORDER BY assetid;

CodePudding user response：

you need to use max function:

SELECT
    inspection.flag,
    asset.assetid,
    asset.length,
    max(inspection.date)
FROM asset left join inspection on asset.assetid = inspection.assetid
GROUP BY 1, 2, 3