How to pull the newest record for each student out of a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill - 22 Dec 2005 01:38 GMT

Can I do this in one SQL statement? If so, please tell me how. I
have wasted a ton of time on this but there has to be an easy way to
do it. I should have done it with more than one statement and been
done with it but I can't stop thinkin about it.

Table:
Grades

Columns:
StudentID
GradeDate
Grade

I want to get the last grade for each student. However, the maximum
date for each student is different. I tried

SELECT max(GradeDate), StudentID, Grade
FROM Grades;

Didn't give me what I wanted.

Please tell me how to get the newest grade for each student.

Bill

Reply to this Message

pietlinden@hotmail.com - 22 Dec 2005 06:17 GMT

Is this a homework assignment? Did you forget to read the section on
summary queries?

Reply to this Message

Bill - 22 Dec 2005 23:38 GMT

>Is this a homework assignment? Did you forget to read the section on
>summary queries?

Nope. I've just forgotten how to do it.

I have the answers I need but I feel like I did it the long way. But
what is the simplest way? Can I do it in one statement?

It's not exactly what I would cosider a summary. I just want the
latest record for each ID in the table.

Reply to this Message

Randy Harris - 23 Dec 2005 00:41 GMT

> Can I do this in one SQL statement? If so, please tell me how. I
> have wasted a ton of time on this but there has to be an easy way to
[quoted text clipped - 20 lines]
>
> Bill

Bill, like most all of this stuff, there's more than one way to do it.
There's probably better ways, as well, but this is what I came up with:

SELECT StudentID, GradeDate, Grade
FROM Grades INNER JOIN
(SELECT Max(GradeDate) AS MaxDate, StudentID
FROM Grades GROUP BY StudentID) as g
ON (Grades.StudentID=g.StudentID) AND (Grades.GradeDate = g.MaxDate);

HTH

Signature

Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Reply to this Message

Bill - 23 Dec 2005 01:19 GMT

>> Can I do this in one SQL statement? If so, please tell me how. I
>> have wasted a ton of time on this but there has to be an easy way to
[quoted text clipped - 31 lines]
>
>HTH

Thank you.

That's just what I needed.

Reply to this Message

MGFoster - 27 Dec 2005 22:40 GMT

Try this:

SELECT StudentID, Grade
FROM Grades As G
WHERE GradeDate = (SELECT MAX(GradeDate) FROM Grades
WHERE StudentID = G.StudentID)

This is an example of a correlated subquery. The subquery retrieves the
latest (max) GradeDate for each StudentID in the main query.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Can I do this in one SQL statement? If so, please tell me how. I
> have wasted a ton of time on this but there has to be an easy way to
[quoted text clipped - 20 lines]
>
> Bill

Reply to this Message