How to find latest date/time in grouped rows

  • Thread starter Thread starter Dorian
  • Start date Start date
D

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".
 
Dorian said:
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.
Click to expand...


SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)
 
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".


Marshall Barton said:
Dorian said:
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.
Click to expand...


SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)
Click to expand...
 
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".


Marshall Barton said:
Dorian said:
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.
Click to expand...

SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)
Click to expand...
Click to expand...
 
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".


Marshall Barton said:
Dorian said:
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.
Click to expand...


SELECT ID, TEAM, [DATE]
FROM table
WHERE [DATE] = (SELECT Max(X.DATE])
FROM table As X)
Click to expand...
Click to expand...
 
Marshall said:
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
Click to expand...


That Totals query should have been:

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