Sorting Query Results

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.

Sorting a result set using the ORDER BY clause

Figure 4-1. Sorting a result set using the ORDER BY clause

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.