When
you use a basic SQL SELECT
statement to retrieve
records from a database, those records are returned in the order in
which they are stored. If you want to change the order in which the
records are displayed (which you probably do), you need to use an
ORDER BY
clause, as shown in Example 4-2.
Example 4-2. Sorting query results using the SQL ORDER clause
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF"> SELECT Name, Title, Department, Email, PhoneExt FROM EmployeeDirectory ORDER BY Name ASC </cfquery> <html> <head> <title>Sorting Query Results Using the SQL ORDER Clause</title> <style type="text/css"> th { background-color : #888888; font-weight : bold; text-align : center; } td { background-color : #C0C0C0; } </style> </head> <body> <table cellpadding="3" cellspacing="1"> <tr> <th>Name</th> <th>Title</th> <th>Department</th> <th>E-mail</th> <th>Phone Extension</th> </tr> <cfoutput query="GetEmployeeInfo"> <tr> <td>#Name#</td> <td>#Title#</td> <td>#Department#</td> <td><a href="Mailto:#Email#">#Email#</a></td> <td>#PhoneExt#</td> </tr> </cfoutput> </table> </body> </html>
The ORDER BY
clause specifies which column or
columns to use in ordering the query results. Sorting can be either
ASC
(ascending) or DESC
(descending). Example 4-2 sorts the result set by
name
column, in ascending order. The output is
shown in Figure 4-1.
Multicolumn sorts can be performed by specifying a comma-delimited
list of column names and sort orders for the ORDER
BY
clause as in:
<cfquery name="GetEmployeeInfo" datasource="ProgrammingCF"> SELECT Name, Title, Department, Email, PhoneExt FROM EmployeeDirectory ORDER BY Title ASC, Name ASC </cfquery>
Get Programming ColdFusion MX, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.