Yeah, for my needs, I used the following (for our Oracle backend database)..
SELECT *
FROM
(
SELECT rownum, {various fields}
FROM {some joined tables}
WHERE {some criteria}
ORDER BY dbms_random.value
)
WHERE rownum = {however many I needed}
-----Original Message-----
From: Jochem van Dieten [mailto:[email protected]]
Sent: Wednesday, August 03, 2011 3:08 PM
To: cf-talk
Subject: Re: COMPLEX SQL RANDOM
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:346516
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
