Tom, In Access, something like the following works for us, where 'PrimaryKey' is a unique key in the table:
SELECT * FROM CompanyTbl WHERE CompanyWantsMiniSite = 'Yes' AND MakeMiniSitePublic = 'Yes' ORDER BY ClassOfService, rnd(PrimaryKey) If you're not seeing the expected number of records, I'd examint the WHERE clause, as the sort order should have no effect on this. Jim ----- Original Message ----- From: "Tom Forbes" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, April 03, 2003 4:11 PM Subject: RE: How can I "ORDER BY RANDOM" ? > This method does return a random order, but there should be 7 records > returned, and there is a random number returned! > > Tom > > > > At 10:48 AM 4/4/03 +1200, you wrote: > >You'd have to write > > SELECT CompanyTbl.* > > FROM CompanyTbl > > WHERE CompanyWantsMiniSite = 'Yes' AND MakeMiniSitePublic = 'Yes' > > ORDER BY CompanyTbl.ClassOfService, rnd() > > > >Some dbms's will happily generate the same random number for each row. I > >know this works in SQL Server: > > SELECT * > > FROM myTable > > ORDER BY NEWID() > > > > > > > > > -----Original Message----- > > > From: Tom Forbes [mailto:[EMAIL PROTECTED] > > > Sent: Friday, 4 April 2003 10:45 a.m. > > > To: CF-Talk > > > 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
