Could try using HAVING;
...., (
SELECT QuizPercent FROM test_module as t1
WHERE t1.Customer_ID = 21
AND t1.QuizID = test_module.QuizID
HAVING t1.QuizDate = MAX(Day(test_module.QuizDate))
AND t1.QuizDate = MAX(Time(test_module.QuizDate)) QuizPerent
> 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
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294163
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
