Given a table of data like this:

Given a table of data like this:

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:

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

• sql
• sql-server
• greatest-n-per-group

• Please tag your RDBMS, see here why that's important: meta.stackoverflow/questions/388759/… – Bart McEndree Commented Nov 18, 2024 at 14:15

2 Answers 2

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

There are some hacky and some more standard solutions.

Standard is to get the last value in a subquery and to aggregate it later, something like:

select a, STRING_AGG(b, ',') WITHIN GROUP(ORDER BY a,c) as b, c, max(lastD) as d
from (
    select a, b,c,d, e, last_value(d) over(partition by a,b,c order by e desc) as lastD
    from (
        VALUES  (1, N'test', 9, N'h', N'2024-10-22 08:00:00.000')
        ,   (1, N'test', 9, N'l', N'2024-10-23 08:00:00.000')
        ,   (1, N'test', 9, N'q', N'2024-10-22 08:00:00.000')
    ) t (a,b,c,d,e)
    ) x
GROUP BY a,b,c

The hacky way is something i'd call reconstruction, which entails combining your aggregation value and then deconstructing it back after retrieving the maximum value, something like:

SELECT  a, STRING_AGG(b, ',') WITHIN GROUP(ORDER BY a,c) AS b, c
,   STUFF(MAX(CONCAT(CONVERT(VARCHAR(30), cast(e AS datetime), 121), d)), 1,23, '') AS d
FROM
(
    VALUES  (1, N'test', 9, N'h', N'2024-10-22 08:00:00.000')
    ,   (1, N'test', 9, N'l', N'2024-10-23 08:00:00.000')
    ,   (1, N'test', 9, N'q', N'2024-10-22 08:00:00.000')
    ) t (a,b,c,d,e)
GROUP BY a,b,c

Here, by combining a varchar representation of the date and your d value, one gets a natural ascending value by the date, so one can use MAX. After getting the highest value, one can use STUFF function to remove the date part and get the d value.

This has some caveats especially if you concat non-string columns. Also, it's not possible to use tie-breakers if you have multiple of the same date. The upside is that it avoids the extra window aggregation step.