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

[Bill]

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

[pietlinden]

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

[Bill]

On 21 Dec 2005 22:17:21 -0800, pi********@hotmail.com wrote:

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.

[Randy Harris]

"Bill" <Bi*****@yahoo.com> wrote in message
news:2m********************************@4ax.com...

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

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

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

[Bill]

On Fri, 23 Dec 2005 00:41:56 GMT, "Randy Harris" <ra***@SpamFree.com>
wrote:

"Bill" <Bi*****@yahoo.com> wrote in message
news:2m********************************@4ax.com.. .

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

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

Thank you.

That's just what I needed.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ7HC1IechKqOuFEgEQJQ4ACg25vXisU7IWbeTpAKW8uc7/d3CkEAoL2A
yEt2NA3/1rod2N8iq8ZCQq/C
=iYDC
-----END PGP SIGNATURE-----

Bill wrote:

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