SELECT MAX(QuizDate) as QuizDate, QuizID,
QuizPercent = (SELECT QuizPercent FROM Test_Board_Biomed as t1
WHERE t1.Customer_ID = 21
and t1.QuizID = Test_Board_Biomed.QuizID
and t1.QueryDate = MAX(Test_Board_Biomed.QuizDate))
FROM Test_Board_Biomed
WHERE Customer_ID = 21
GROUP BY QuizID
If this doesn't work (the "t1.QueryDate = MAX(Test_Board_Biomed.QuizDate)" part looks sketchy to me, but it might work) then you can use a subquery with a join:
SELECT t2.QuizDate, t2.QuizID, t1.QuizPercent
FROM
(SELECT MAX(QuizDate) as QuizDate, QuizID,
FROM Test_Board_Biomed
WHERE Customer_ID = 21
GROUP BY QuizID) as t2
JOIN Test_Board_Biomed as t1
ON (t1.Customer_ID = t2.Customer_ID
and t1.QuizID = t2.QuizID
and t1.QueryDate = t2.QuizDate)
Give that a shot.
Mark
-----Original Message-----
From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 14, 2004 2:27 PM
To: CF-Talk
Subject: MAX() Select Problem
Having problems with a select query:
SELECT MAX(QuizDate) as QuizDate, QuizID
FROM Test_Board_Biomed
WHERE Customer_ID = 21
GROUP BY QuizID
The above query returns:
QuizDate QuizID
2004-10-12 Antacids
2004-10-13 Sleep
2004-10-12 Stool
What I want is something like
QuizDate QuizID QuizPercent
2004-10-12 Antacids 53
2004-10-13 Sleep 62
2004-10-12 Stool 61
I am having a hard time selecting the QuizPercent associated with
the Max(QuizDate). Basicaly, I want to extract the Score that was
received on the last date the test was taken.
SELECT MAX(QuizDate) as QuizDate, QuizID, QuizPercent
FROM Test_Board_Biomed
WHERE Customer_ID = 21
GROUP BY QuizID , QuizPercent
The above query returns
QuizDate QuizID QuizPercent
2004-10-11 Sleep 25
2004-10-11 Sleep 37
2004-10-11 Antacids 46
2004-10-12 Antacids 53
2004-10-12 Stool 61
2004-10-13 Sleep 62
2004-10-12 Sleep 75
2004-10-12 Sleep 87
2004-10-12 Sleep 100
What am I doing wrong? Or is it that it's not possible to have
this kind of query?
Sebastian
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
