How to Display in Random Order in ColdFusion

Results of a ColdFusion query can be randomized by seeding its controlling SQL statement.

There are many reasons why you might want to display the results of a query in random order in a ColdFusion Web application. Perhaps your page calls for a list of businesses and their contact details, and you don't wish to give preference to those whose names come first in the alphabet by displaying them at the top of your page. Fortunately, using a combination of Coldfusion's query tag and SQL protocols, you can order your query results randomly.

Instructions

1. Open the page on which you wish to display the randomized results. Set up a query as follows:

   <cfquery name="YourName" datasource="YourDataSource"

   SELECT TableColumn

   FROM Table

   </cfquery>

2. Add the following seeding statement to the SQL language after your FROM parameter:

   ORDER BY NEWID()

   This will randomize the order of the query results.

3. Call the cfquery in the body of the page using the following code:

   <cfoutput query="YourName">#TableColumn#</cfoutput>

   This will display the randomized results one line at a time.

Tips & Warnings

• The ORDER BY NEWID() method for sorting the list will only work in a SQL Server environment. If you are instead using a MySQL platform, replace it with ORDER BY RAND(). If you are using SQLite, replace it with ORDER BY Random. If you are using PostgreSQL, replace it with ORDER BY RANDOM(). For other database management systems, consult the appropriate documentation for a replacement statement.

References

• Adobe Developer Center; CFQuery
• SQL: Visual QuickStart Guide; Chris Fehily; 2008