finding max date within groups within a query 1

[cdipaolo]

I have a table that looks like this:

State, Location, Source, Date
TX, Houston, 1, 1/1/05
TX, Houston, 1, 12/1/04
TX, Houston, 2, 2/1/05
TX, Houston, 2, 3/1/05
TX, Dallas, 1, 1/1/05

etc.

Basically I want to be able to go through the table and find the latest date for each location and for each source, so based on the above I would only return:
TX, HOuston, 1, 1/1/05
TX, Houston, 2, 3/1/05
TX, Dallas, 1, 1/1/05

Is there a way to do this without breaking it up into multiple queries or stepping through the records in VBA?

 

[traingamer]

Try:

Select State, Location, Source, Max(date) as MaxDate
from table
Group by State, Location, Source

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[cdipaolo]

That answers that question perfectly - the only problem I have (and I didn't think about this when posting) is that each row of the table has a value that I want to return as well. Each reporting date will have a different score so I want to include the score that was reported on that reporting date. Do I just join the original table back to the query that you show?

 

[traingamer]

That's one way that would work or you may try adding the aggregate function Last([score]) which may work depending on how your data is entered.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

SELECT A.State, A.Location, A.Source, A.Date, A.Score
FROM yourTable AS A INNER JOIN (
SELECT State, Location, Source, Max([Date]) AS MaxDate
FROM yourTable GROUP BY State, Location, Source
) AS B ON A.State=B.State AND A.Location=B.Location AND A.Source=B.Source AND A.Date=B.MaxDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244