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
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
a | b | c | d |
---|---|---|---|
1 | test | 9 | l |