June 7, 2004 at 12:23 pm
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
June 7, 2004 at 2:46 pm
It looks like if I handle the TestDate field in a CASE statement as follows:
"MaxDate" = Case
When Max(TestDate) is not null then Max(TestDate) End
The I get the result I was after.
CSDunn
June 7, 2004 at 3:03 pm
As it turns out, I spoke to soon, this did not work.
Does any one else have and idea on this? I'm still trying some things.
Thank you!
CSDunn
June 7, 2004 at 4:38 pm
Simple you can do a subquery for the maxdate and student and join this to the table itself for the rest. This assumes however the test can only be taken by a student once per date.
Ex.
SELECT columnlist FROM dbo.tblX A
INNER JOIN
(Select StudentID,Max(DateTakeN) MDateTaken FROM dbo.tblX GROUP BY StudentID) B
ON
A.StudentID = B.StudentID AND
A.DateTaken = B.MDateTaken
...
June 8, 2004 at 9:15 am
Thanks, I will try this. I was able to do something similar by using my CASE stmt in a view, then joined to it by StudentID and TestDate in another query.
CSDunn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply