Programming ColdFusion

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 were originally entered. If you want to change the order in which the records are displayed, 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>

<TABLE CELLPADDING="3" CELLSPACING="0">
<TR BGCOLOR="#888888">
   <TH>Name</TH>
   <TH>Title</TH>
   <TH>Department</TH>
   <TH>E-mail</TH>
   <TH>Phone Extension</TH>
</TR>    
<CFOUTPUT QUERY="GetEmployeeInfo">
<TR BGCOLOR="##C0C0C0">
   <TD>#Name#</TD>
   <TD>#Title#</TD>
   <TD>#Department#</TD>
   <TD><A HREF="Mailto:#Email#">#Email#</A></TD>
   <TD>#PhoneExt#</TD>
</TR>    
</CFOUTPUT>
</TABLE>

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.

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"> ...