sqlsql-servergreatest-n-per-group

In a select with a group by, get the last value of a column not in the group by


Given a table of data like this:

a b c d e
1 test 9 h 2024-10-22 08:00:00.000
1 test 9 l 2024-10-23 08:00:00.000
1 test 9 q 2024-10-22 08:00:00.000

I want to group my data by columns a,b,c and show the value form column d the has the newest date in column e.

So I would expect to get one row of data back like this:

a b c d
1 test 9 l

I would have liked something as simple as a "last()" like below but as far as I can find there isn't anything so simple?

SELECT 
    a, b, c,
    last(d)
FROM
    dbo.items 
GROUP BY 
    a, b, c

The only example I can find remotely close to what I want is a LAST_VALUE OVER PARTITION it doesn't work in a group by

LAST_VALUE(d) OVER (PARTITION BY d ORDER BY e) AS d

And I know similar things are possible to access the stuff not in a group by, like if b want in the group by I would still be able to STRING_AGG all the values like so

STRING_AGG(b, ',') AS b 

and get "test,test,test" as the value


Solution

  • Using Row_Number might work if you are using SQL Server

    SELECT a, b, c, d FROM
    (SELECT *,
          ROW_NUMBER() OVER (PARTITION BY a,b,c ORDER BY e desc) as rn
    FROM dbo.items 
    )t
    WHERE rn=1
    

    fiddle

    a b c d
    1 test 9 l