Hi Jim, Thanks, That did it! BUT, it works great locally with CF Studio, but when I upload it the list does not change order. It stays with the first random order it generated. It's not a cached query. I reload and reload, but no change in the order. Granted, it is a random order, but it stays the same.
Any ideas here?? Sorry to be a pain. Tom At 04:46 PM 4/3/03 -0700, you wrote: >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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
