On Wed, Aug 3, 2011 at 8:43 PM, Robert Harrison wrote:
> Normally I would use TOP 1 and ORDER BY NEWID() to do this. But it doesn't
> like that with the DISTINCT clase. I'm using DISTINCT because the same poll
> could come up several times if it's allowed in multiple groups and that use
> is in several of those groups. And I would drop the DISTINCT but if one poll
> comes up a bunch of times that would increase the likelihood it would be
> selected at random.
The reason for this skew is the order of execution: first you
calculate matches (resulting in multiple rows for some polls), then
you assign each a newID(). The solution is to assign the newID() first
(when each poll only occurs once) and then calculate the matches. One
of the most readable ways to force an execution order is with a CTE,
which should look something like:
-- define a CTE open_polls
WITH open_polls AS
(
SELECT
portal_polls.*
, NewID() as randomNumber
FROM
portal_polls
WHERE
-- with polls that are currently open
portal_polls.start_date < #Now()#
and
portal_polls.end_date > #Now()#
-- and have not been completed by this user
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#
)
)
-- from which we select one row
SELECT TOP 1 open_polls.poll_id, open_polls.question,
open_polls.answer1, open_polls.answer2, open_polls.answer2,
open_polls.answer4, open_polls.answer4, open_polls.number_answers
FROM open_polls, portal_groups_x_polls, portal_groups_x_students
WHERE
(everyone=1
OR (
portal_groups_x_polls.poll_id=open_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#
)
)
ORDER BY
open_polls.randomNumber;
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:346501
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
