Since the query you have gives you the list of polls that you want, what about dumping the results of that query into a temp table, and doing a random select on the temp table with SELECT TOP 1 and ORDER BY NEWID() ?
Steve On Wed, Aug 3, 2011 at 2:43 PM, Robert Harrison <[email protected]>wrote: > > 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:346496 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
