Need MAX from Each Group

Hello,

I have a table called 'tblCLTestScores' that holds student test scores
for one particular test. Students take this same test three times on
different dates, so the table contains three or fewer records for each
student. The records for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.

I need a query that will, for every student, extract the record with
the MAX 'TestDate'.

The fields that I need to query from 'tblCLTestScores' are as
follows:

TS. StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
TS.BookID,
TS.TestDate,
TS.SOLevel

I've tried a couple of things using MAX(TS.TestDate), but I always get
the maximum TestDate for all of the records, and not the maximum
TestDate for each student.

How do I set up the query to ge the MAX TestDate for each student?

Thank you for your help!

CSDunnHi

It is not clear how you differentiate testA from testB Assuming you have a
TestId try something like:

SELECT
TS.StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
TS.BookID,
TS.TestDate,
TS.SOLevel
FROM tblCLTestscores TS JOIN
( SELECT StudentID, TestId, MAX(TestDate) AS TestDate
FROM tblCLTestscores
GROUP BY StudentID, TestId ) DT ON TS.StudentID = DT.StudentID AND TS.TestId
= DT.TestId
AND TS.TestDate =DT.TestDate

John

"CSDunn" <cdunn@.valverde.edu> wrote in message
news:807dbff7.0406071022.28823376@.posting.google.c om...
> Hello,
> I have a table called 'tblCLTestScores' that holds student test scores
> for one particular test. Students take this same test three times on
> different dates, so the table contains three or fewer records for each
> student. The records for any given student are made distinct by the ID
> number of the test booklet, and the date on which they took the test.
> I need a query that will, for every student, extract the record with
> the MAX 'TestDate'.
> The fields that I need to query from 'tblCLTestScores' are as
> follows:
> TS. StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
> TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
> TS.BookID,
> TS.TestDate,
> TS.SOLevel
> I've tried a couple of things using MAX(TS.TestDate), but I always get
> the maximum TestDate for all of the records, and not the maximum
> TestDate for each student.
> How do I set up the query to ge the MAX TestDate for each student?
> Thank you for your help!
> CSDunn|||>> I have a table called 'tblCLTestScores' ... <<

Take off that silly, redundant "tbl-" prefix that you added in
violation of ISO-11179 Standards.

>> .. that holds student test scores for one particular test. Students
take this same test three times on different dates, so the table
contains three or fewer records [sic] for each student. <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. They are totally different
concepts.

>> The records [sic] for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.
<<

A great natural key!

>> I need a query that will, for every student, extract the record
with the MAX 'TestDate'. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You ave us a bunch of qualified names without so much
as a table.

>> How do I set up the query to ge the MAX TestDate for each student?
<<

SELECT S1.student_id, ...
FROM CLTestScores AS S1
WHERE test_date
= (SELECT MAX(S2.test_date)
FROM CLTestScores AS S2
WHERE S1.student_id = S2.student_id);|||>> I have a table called 'tblCLTestScores' ... <<

Take off that silly, redundant "tbl-" prefix that you added in
violation of ISO-11179 Standards.

>> .. that holds student test scores for one particular test. Students
take this same test three times on different dates, so the table
contains three or fewer records [sic] for each student. <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. They are totally different
concepts.

>> The records [sic] for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.
<<

A great natural key! Verifiable, clean, etc. But please post DDL, so
that people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.
You ave us a bunch of qualified names without so much as a table.

>> How do I set up the query to get the MAX(test_date) for each
student? <<

SELECT S1.student_id, ...
FROM CLTestScores AS S1
WHERE test_date
= (SELECT MAX(S2.test_date)
FROM CLTestScores AS S2
WHERE S1.student_id = S2.student_id);