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#);
)
-----Original Message-----
From: Steve Milburn [mailto:[email protected]]
Sent: Wednesday, August 03, 2011 2:33 PM
To: cf-talk
Subject: Re: COMPLEX SQL RANDOM
The error message you are getting is because the value of your order by
clause, newID(), is not a part of your list of values you are selecting.
You could, of course, include it in your list of values by first setting it
to a variable, like so:
declare @orderClause as uniqueidentifier;
set @orderClause = NEWID();
select distinct top 1 fieldA, fieldB, @orderClause as fieldC
from table
order by fieldC
This would get you past the error you are receiving about the ORDER BY
clause must appear in the select list. However, because every row in your
resulting recordset now has a field called fieldC with the value of your
previously generated ID, you will lost your randomness as the first row will
always be returned. Experiment on a simpler table than what you are working
with using the above code as a guide and you'll see what I mean.
I'm not sure you can do this in 1 step. I would still recommend the
temporary table (rather, the derived table as pointed out by Leigh).
Steve
On Wed, Aug 3, 2011 at 2:54 PM, Robert Harrison
<[email protected]>wrote:
>
> > 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() ?
>
> I have considered that, but this should be possible in one straight up SQL
> statement. It's annoying me and I'm being anal... but I want it right.
>
>
> 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/unplugged
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:346500
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
