How to find latest date/time in grouped rows

[Dorian]

I have a table with id, team, date
I need to select the id and team with the latest date
team can appear multiple times.

I tried SELECT ID, TEAM, MAX(DATE) GROUP BY ID but then it expects TEAM to
have an aggregate function. I just need the team on the record with the
latest date.

Seems like it should be simple but how?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Marshall Barton]

I have a table with id, team, date
I need to select the id and team with the latest date
team can appear multiple times.

I tried SELECT ID, TEAM, MAX(DATE) GROUP BY ID but then it expects TEAM to
have an aggregate function. I just need the team on the record with the
latest date.

SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)

 

[Dorian]

That wont do it...probably because I explained the problem poorly.
The id is not unique and I need the id, team and date where the date is the
latest date for the id. E.G.
1 TeamA 1/1/2009 -- gets chosen
1 TeamB 12/25/2008
2 TeamA 1/1/2008
2 TeamC 1/1/2009 -- gets chosen
essentially I'm looking for the last team assigned to a problem(id).
so I definitely think there has to be a 'group by' somewhere.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I have a table with id, team, date
I need to select the id and team with the latest date
team can appear multiple times.

I tried SELECT ID, TEAM, MAX(DATE) GROUP BY ID but then it expects TEAM to
have an aggregate function. I just need the team on the record with the
latest date.

SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)

 

[John Spencer (MVP)]

Use a correlated sub query in the where clause. Very close to Marshall's
suggestion.

SELECT *
FROM YourTable as Y1
WHERE [Date] =
(SELECT Max([Date]) as LastDate
FROM YourTable as Y2
WHERE Y2.ID = Y1.ID)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

That wont do it...probably because I explained the problem poorly.
The id is not unique and I need the id, team and date where the date is the
latest date for the id. E.G.
1 TeamA 1/1/2009 -- gets chosen
1 TeamB 12/25/2008
2 TeamA 1/1/2008
2 TeamC 1/1/2009 -- gets chosen
essentially I'm looking for the last team assigned to a problem(id).
so I definitely think there has to be a 'group by' somewhere.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I have a table with id, team, date
I need to select the id and team with the latest date
team can appear multiple times.

I tried SELECT ID, TEAM, MAX(DATE) GROUP BY ID but then it expects TEAM to
have an aggregate function. I just need the team on the record with the
latest date.

SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)

 

[Marshall Barton]

You could put the ID condition in the subquery's Where
clause:

SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X
WHERE X.ID = table.ID)

BUT, if there is exactly one ID for each TEAM and you do not
want any other fields, then it would be better to use a
Totals type query:

SELECT ID, TEAM, [DATE], Max([DATE])
FROM table
GROUP BY ID, TEAM
--
Marsh
MVP [MS Access]

That wont do it...probably because I explained the problem poorly.
The id is not unique and I need the id, team and date where the date is the
latest date for the id. E.G.
1 TeamA 1/1/2009 -- gets chosen
1 TeamB 12/25/2008
2 TeamA 1/1/2008
2 TeamC 1/1/2009 -- gets chosen
essentially I'm looking for the last team assigned to a problem(id).
so I definitely think there has to be a 'group by' somewhere.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I have a table with id, team, date
I need to select the id and team with the latest date
team can appear multiple times.

I tried SELECT ID, TEAM, MAX(DATE) GROUP BY ID but then it expects TEAM to
have an aggregate function. I just need the team on the record with the
latest date.

SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)

 

[Marshall Barton]

You could put the ID condition in the subquery's Where
clause:

SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X
WHERE X.ID = table.ID)

BUT, if there is exactly one ID for each TEAM and you do not
want any other fields, then it would be better to use a
Totals type query:

SELECT ID, TEAM, [DATE], Max([DATE])
FROM table
GROUP BY ID, TEAM

That Totals query should have been:

SELECT ID, TEAM, Max([DATE])
FROM table
GROUP BY ID, TEAM