471,593 Members | 1,774 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,593 software developers and data experts.

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

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
Dec 22 '05 #1
5 3532
Is this a homework assignment? Did you forget to read the section on
summary queries?

Dec 22 '05 #2
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.
Dec 22 '05 #3

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

Dec 23 '05 #4
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.
Dec 23 '05 #5
-----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

Dec 27 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by MUHAMAMD SALIM SHAHZAD | last post: by
7 posts views Thread by R. Rajesh Jeba Anbiah | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.