On Wed, Aug 3, 2011 at 9:36 PM, Bill Franklin wrote: > Why not try something like this...(not sure which database you are using) > > SELECT TOP 1 * > FROM > ( > SELECT DISTINCT portal_polls.poll_id, portal_polls.question, > portal_polls.answer1, portal_polls.answer2, portal_polls.answer2, > portal_polls.answer4, portal_polls.answer4, portal_polls.number_answers > FROM portal_polls, portal_groups_x_polls, portal_groups_x_students > WHERE (portal_polls.start_date < #Now()# and portal_polls.end_date > > #Now()#) > and (everyone=1 OR > (portal_groups_x_polls.poll_id=portal_polls.poll_id and > portal_groups_x_students.group_id=portal_groups_x_polls.group_id and > portal_groups_x_students.student_user_id=#session.userid#)) > and not exists (SELECT * FROM > portal_students_x_polls_completed WHERE > portal_students_x_polls_completed.poll_id=portal_polls.poll_id and > portal_students_x_polls_completed.student_user_id=#session.userid#); > )
Because this query does not have a random result. The result of the outer query will always be the first row of the inner query. The result of the inner query will be in a predictable order based on the indexes and statistics (probably whichever row is returned first by the index over poll_id, start_date and end_date as I suspect that will be the driving index). Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346503 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
