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
<rob...@austin-williams.com>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:346499
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
