i have the following sql:
SELECT MAX(QuizDate) as QuizDate, QuizID,
QuizPercent = (SELECT QuizPercent FROM test_module as t1
WHERE t1.Customer_ID = 21
AND t1.QuizID = test_module.QuizID
AND t1.QuizDate = MAX(test_module.QuizDate)
FROM test_module
WHERE Customer_ID = 21
GROUP BY QuizID
it works fine until the QuizPercent subquery returns more than one record when
the user has, for example, multiple 100% scores. so i thought of isolating and
looking for max date and max time since the same scores would be done at a
different time by doing the following:
SELECT MAX(QuizDate) as QuizDate, QuizID,
QuizPercent = (SELECT QuizPercent FROM test_module as t1
WHERE t1.Customer_ID = 21
AND t1.QuizID = test_module.QuizID
AND t1.QuizDate = MAX(Day(test_module.QuizDate))
AND t1.QuizDate = MAX(Time(test_module.QuizDate))
FROM test_module
WHERE Customer_ID = 21
GROUP BY QuizID
but in SQL there is no
MAX(Day(test_module.QuizDate))
AND t1.QuizDate = MAX(Time(test_module.QuizDate))
and it generates an error.
suggestions?
sebastian
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Enterprise web applications, build robust, secure
scalable apps today - Try it now ColdFusion Today
ColdFusion 8 beta - Build next generation apps
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294151
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
