Querying for a unique value based on the aggregate of another value while grouping on a third value
- by Justin Swartsel
So I know this problem isn't a new one, but I'm trying to wrap my head around it and understand the best way to deal with scenarios like this.
Say I have a hypothetical table 'X' that looks like this:
GroupID ID (identity) SomeDateTime
--------------------------------------------
1 1000 1/1/01
1 1001 2/2/02
1 1002 3/3/03
2 1003 4/4/04
2 1004 5/5/05
I want to query it so the result set looks like this:
----------------------------------------
1 1002 3/3/03
2 1004 5/5/05
Basically what I want is the MAX SomeDateTime value grouped by my GroupID column. The kicker is that I DON'T want to group by the ID column,
I just want to know the 'ID' that corresponds to the MAX SomeDateTime.
I know one pseudo-solution would be:
;WITH X1 as (
SELECT MAX(SomeDateTime) as SomeDateTime, GroupID
FROM X
GROUP BY GroupID
)
SELECT X1.SomeDateTime, X1.GroupID, X2.ID
FROM X1
INNER JOIN X as X2
ON X.DateTime = X2.DateTime
But this doesn't solve the fact that a DateTime might not be unique. And it seems sloppy to join on a DateTime like that.
Another pseudo-solution could be:
SELECT X.GroupID, MAX(X.ID) as ID, MAX(X.SomeDateTime) as SomeDateTime
FROM X
GROUP BY X.GroupID
But there are no guarantees that ID will actually match the row that SomeDateTime comes from.
A third less useful option might be:
SELECT TOP 1 X.GroupID, X.ID, X.SomeDateTime
FROM X
WHERE X.GroupID = 1
ORDER BY X.SomeDateTime DESC
But obviously that only works with a single, known, GroupID. I want to be able to join this result set on GroupID and/or ID.
Does anyone know of any clever solutions? Any good uses of windowing functions?
Thanks!
