Thanks Marc (and Dan and everyone who helped). I used the order by 4th as you said, and it works great! Huge performance increase too. ;^)
Blessings, Graham ----- Original Message ----- From: "Marc Campeau" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 31, 2002 1:19 PM Subject: RE: [CFTALKTor] random selection from database > For some reason the order by gives this error in Access, try order by 4 > which means "order by the 4th column" and it should work. > > Marc > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > > Behalf Of Graham Faulkner > > Sent: Friday, May 31, 2002 1:11 PM > > To: [EMAIL PROTECTED] > > Subject: Re: [CFTALKTor] random selection from database > > > > > > Hi Marc, > > > > Sorry, I sent the previous post before I read this one about > > rnd(). I'm not > > getting an error about rand() not being a function. Now I'm getting ... > > > > ODBC Error Code = 07001 (Wrong number of parameters) > > [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. > > > > ...when I run the following code: > > > > <cfquery name="getRandomPhoto" datasource="dsn"> > > SELECT TOP 1 PhotoID, PostDate, ImageName, rnd(PhotoID) as orderCol > > FROM photos > > ORDER BY orderCol #IIF(getTickCount() MOD 2, DE("ASC"), DE("DESC"))# > > </cfquery> > > > > <!--- Output Random Photo ---> > > <cfoutput query="getRandomPhoto"> > > <p align="center"><img src="images/photos/#ImageName#" > > width="150"><br>#DateFormat(PostDate, "mmm d, yyyy")#</p> > > </cfoutput> > > > > I'm obviously not familiar with the SELECT TOP syntax, so I have > > a feeling I > > messed it. I used PhotoID as the "unique_ID column" for the > > rnd() function > > as you mentioned in your email. > > > > Any obvious errors? > > > > Blessings, > > > > Graham > > > > > > ----- Original Message ----- > > From: "Marc Campeau" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Friday, May 31, 2002 12:37 PM > > Subject: RE: [CFTALKTor] random selection from database > > > > > > > Hi Graham, > > > > > > Try rnd(either a unique_ID column or NewID()) instead of rand(). > > > > > > > Thanks for the idea. I'm getting an error re: the 'rand' function > > though. > > > > I suspect it's because Access2000 cannot handle it? I did change the > > rest > > > > of the SQL to match my table fields, so I'm GUESSING it's not > > > > with that part > > > > of the syntax. > > > > > > > > > I assumed you were using SQL server... > > > > > > Marc > > > > > > - > > > You are subscribed to the CFUGToronto CFTALK ListSRV. > > > This message has been posted by: "Marc Campeau" <[EMAIL PROTECTED]> > > > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > > > Manager: Kevin Towes ([EMAIL PROTECTED]) > > http://www.CFUGToronto.org/ > > > This System has been donated by Infopreneur, Inc. > > > (http://www.infopreneur.net) > > > > > > > - > > You are subscribed to the CFUGToronto CFTALK ListSRV. > > This message has been posted by: "Graham Faulkner" > > <[EMAIL PROTECTED]> > > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > > Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "Marc Campeau" <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) > - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Graham Faulkner" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
