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 ***********************************************************************

Remove the CompanyName field from the ORDER BY clause.  Then the results will be ordered by ClassOfService only....unless I'm missing your point ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. bryan@electricedgesystems.com --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com > Dear CF'ers: > > I have searched my CF books for a clue, but am unable to find an example of ----- Excess quoted text cut - see Original Post for more -----

This will generally order them according to the clustered index or the order in which they were added. Either way, it's not random. I would expect to see the same results every time. Benjamin S. Rogers http://www.c4.net/ v.508.240.0051 f.508.240.0057 Remove the CompanyName field from the ORDER BY clause.  Then the results will be ordered by ClassOfService only....unless I'm missing your point ;-) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. bryan@electricedgesystems.com --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com > 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 ----- Excess quoted text cut - see Original Post for more ----- 'Yes' ----- Excess quoted text cut - see Original Post for more ----- *********************************************************************** > 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 > *********************************************************************** > >

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 ----- Excess quoted text cut - see Original Post for more -----

Bryan, I need to display the company names in a random order EACH TIME the query is run. Thanks, Tom At 02:12 PM 4/3/03 -0800, you wrote: ----- Excess quoted text cut - see Original Post for more -----

OK...but you should still remove that field from the ORDER BY clause :-) Granted it may not always return records in a random order, but there is no point in ordering by the CompanyName if you're after random results. Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. bryan@electricedgesystems.com --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com ----- Excess quoted text cut - see Original Post for more ----- ;-) ----- Excess quoted text cut - see Original Post for more ----- example ----- Excess quoted text cut - see Original Post for more ----- 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 ----- Excess quoted text cut - see Original Post for more ----- CF ----- Excess quoted text cut - see Original Post for more ----- 'Yes' ----- Excess quoted text cut - see Original Post for more ----- *********************************************************************** ----- Excess quoted text cut - see Original Post for more ----- *********************************************************************** ----- Excess quoted text cut - see Original Post for more -----

I know, I stated in my question that I wanted to MODIFY the EXISTING query. At 02:26 PM 4/3/03 -0800, you wrote: ----- Excess quoted text cut - see Original Post for more -----

A quick search produced this list of articles: http://www.sqlteam.com/FilterTopics.asp?TopicID=135 Benjamin S. Rogers http://www.c4.net/ v.508.240.0051 f.508.240.0057 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 ***********************************************************************

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: ----- Excess quoted text cut - see Original Post for more -----

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 ----- Excess quoted text cut - see Original Post for more -----

Hi Benjamin, Thanks for the good sql resource. I looked at the 7 articles, the ones that pertain to Access are dead links. I have Access DB. But, this site will come in very handy for other queries. Thanks very much! Tom At 05:33 PM 4/3/03 -0500, you wrote: ----- Excess quoted text cut - see Original Post for more -----

Tom Forbes wrote: > > 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. Usually I do: SELECT  <fields> FROM  <tables> WHERE  <predicates> ORDER BY   Random() It might be that your database does not support that because the Random() function needs to be seeded or else it will generate the same sequence every time. In that case, seed it with a different random number for each row. This different random number is best obtained by taking the primary key (different) and add a number (random). SELECT  <fields> FROM  <tables> WHERE  <predicates> ORDER BY   Random(PrimaryKey + <cfqueryparam cfsqltype="cf_sql_integer" value="#RandRange(1,999999)#">) This might be a little hard on your CPU when you have many records. Jochem

Ivan Latunov (ivan@cfchat.net) wrote a great UDF for doing that with an array, called ArrayShuffle (http://www.cflib.org/udf.cfm?ID=292).  You write the companys into an array, and then it shuffles them.  (I did a similar thing here (http://hscwebdev.unm.edu/webdev/staff/groth/hogwash_form.cfm) to juggle 25 table cells. David Groth, Analyst/Programmer III HSC Library & Informatics Center, University of New Mexico 505.272.8406  e-mail: DGroth@salud.unm.edu >>> tforbes@ix.netcom.com 04/03/03 03:05PM >>> 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 ***********************************************************************

I have version 4.5 Tom At 03:09 PM 4/3/03 -0700, you wrote: ----- Excess quoted text cut - see Original Post for more -----

Leave your query the same. Do something like: <cfset row = randRange(1, GetAllCompaniesWithMiniSites.recordCount)> <cfoutput query="GetAllCompaniesWithMiniSites" startrow="#row#" maxrows="1">      {do processing here} </cfoutput> ----- Excess quoted text cut - see Original Post for more -----

Thanks, I tried this, and it DOES return a list in a random order every time, BUT ... the record set is 7, and the query returns random numbers of results, from 1 to 7! I have the following line that I modified to include your code - could my GROUP parameter causing problems?? Tom <cfoutput query="GetAllCompaniesWithMiniSites" GROUP="ClassOfService" startrow="#row#" maxrows="1"> At 03:32 PM 4/3/03 -0700, you wrote: ----- Excess quoted text cut - see Original Post for more -----

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() ----- Excess quoted text cut - see Original Post for more -----

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: ----- Excess quoted text cut - see Original Post for more -----

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 ----- Excess quoted text cut - see Original Post for more -----

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: ----- Excess quoted text cut - see Original Post for more -----

Ahh, I see.  You want the rows to be randomized. What DB are you using? ----- Excess quoted text cut - see Original Post for more -----

I use Access 97 At 04:19 PM 4/3/03 -0700, you wrote: ----- Excess quoted text cut - see Original Post for more -----

Nice!  Great idea!!!  Very clever... 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 ----- Excess quoted text cut - see Original Post for more -----