Need help bringing back an ID on the most recent record

L

ldmccarty

I have a table with

Student ID EventID EventDate (for example)
573 2670 7/1/2007
573 2716 7/1/2006


I'd like to return the EventID of the last EventDate for each Student.

I've tried many variations of Max and Last, but because I want to
bring back the EventID, I keep doing a GROUPBY on EventID and then I
get multiple rows instead of just the most recent row.

In the example above, I'd want to return

573, 2670 (so that I can join event 2670 to the event table to find
out more about it).

But I keep getting both records returned.

Can anyone help with this query?

Thanks
LD
 
R

Rob Parker

I find that the easiest way to do this is via two steps:
First, a query to return the latest EventDate for each StudentID (call it
qryLatestEvent):
SELECT StudentID, Max(EventDate) AS MaxOfEventDate
FROM YourTableName
GROUP BY StudentID;

Then join this query to the original table, on both StudentID and EventDate,
to get the EventID:
SELECT YourTableName.StudentID, YourTableName.EventID,
YourTableName.EventDate
FROM YouTableName INNER JOIN qryLatestEvent
ON (YourTableName.EventDate = qryLatestEvent.MaxOfEventDate)
AND (YourTableName.StudentID = qryLatestEvent.StudentID);

HTH,

Rob

Maybe some query guru will show you how to do this in a single query,
involving sub-queries; however, I find this two-step process much easier to
follow what's going.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top