I have this SQL statement below.
<cfquery name="random_poll" datasource="#dsn#">
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#);
</cfquery>
In short it selects a list of polls which:
1. Are active (open but not ended) ... thus the start_date, end_date
2. Are open to everyone (everyone) OR open to a group for which this
poll is allowed and in which the user (session.userid) is enrolled... thus the
joins to portal_groups_x_polls and portal_groups_x_students)
3. and Have NOT already been completed by this user... thus the not
exists in portal_students_x_polls_completed
All this is working correctly, but I want to select ONLY 1 poll at RANDOM.
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.
Anyone know how I can adjust this query to select only 1 matching record at
random?
Thanks,
Robert
Robert B. Harrison
Director of Interactive Services
Austin & Williams
125 Kennedy Drive, Suite 100
Hauppauge NY 11788
P : 631.231.6600 Ext. 119
F : 631.434.7022
http://www.austin-williams.com
Great advertising can't be either/or. It must be &.
Plug in to our blog: A&W Unplugged
http://www.austin-williams.com/unplugge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:346494
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
