I don't think I've seen this mentioned yet. Another way you can randomly order your results is to say:
SELECT * FROM TABLE WHERE your criteria here ORDER BY NewId() I've found this works well in SQL Server 2000 but starts to slow down if you have a large number of records. Still, I've done it on 1,000 records or so without any problems. -Novak ----- Original Message ----- From: "Tom Forbes" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, April 03, 2003 2:44 PM Subject: RE: How can I "ORDER BY RANDOM" ? > Hi Russ, > > I did that, but I get an error stating that there is no column in the db > named SortOrder. Here is the modified query: > > <CFQUERY NAME="GetAllCompaniesWithMiniSites" DATASOURCE="MedMallDs" > DBTYPE="ODBC"> > > SELECT CompanyTbl.*, Rnd() as SortOrder > > FROM CompanyTbl > > WHERE CompanyWantsMiniSite = 'Yes' AND MakeMiniSitePublic = 'Yes' > > ORDER BY CompanyTbl.ClassOfService, SortOrder > > </CFQUERY> > > Thanks, > > Tom > > At 04:10 PM 4/3/03 -0600, you wrote: > >If your database has a random number generator function, you can try: > > > >SELECT tblCompany.*, Random() as SortOrder FROM tblCompany ORDER by > >SortOrder > > > >HTH--haven't tried it myself. > > > >Russ > > > > > -----Original Message----- > > > From: Tom Forbes [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, April 03, 2003 4:06 PM > > > To: CF-Talk > > > Subject: How can I "ORDER BY RANDOM" ? > > > > > > > > > Dear CF'ers: > > > > > > I have searched my CF books for a clue, but am unable to find > > > an example of > > > what I am trying to do. > > > > > > I need to modify the below query so that my results are still > > > ordered by > > > "ClassOfService" (a number between 1 and 4) - BUT, the > > > "CompanyName" needs > > > to be displayed in a RANDOM ORDER each time the query is run, > > > rather than > > > its current alphabetical order. This will give all companies that > > > participate an even advantage of being selected rather than > > > those that > > > begin with the first letters of the alphabet. > > > > > > Can someone please point me in the right direction? I have > > > studied the CF > > > RANDOM tag but can't figure out how to apply it. > > > > > > <CFQUERY NAME="GetAllCompaniesWithMiniSites" DATASOURCE="MedMallDs" > > > DBTYPE="ODBC"> > > > > > > SELECT * > > > > > > FROM CompanyTbl > > > > > > WHERE CompanyWantsMiniSite = 'Yes' AND > > > MakeMiniSitePublic = 'Yes' > > > > > > ORDER BY CompanyTbl.ClassOfService, CompanyTbl.CompanyName > > > > > > </CFQUERY> > > > > > > Thanks very much!! > > > > > > Tom Forbes > > > > > > > > > > > > ************************************************************** > > > ********* > > > Tom Forbes, Forbes Consulting, Inc. > > > TEL: 407-772-3427 FAX: 407-772-3428 > > > Visit www.medmatrix.com for Medical Equipment Web Sites! > > > E-Mail Subscription at www.medmatrix.com/mail_list.htm > > > ************************************************************** > > > ********* > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
