Need help bringing back an ID on the most recent record

[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

 

[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.