• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Database Basics > Sorting Query Results

4.5. 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">
         SELECT Name, Title, Department, Email, PhoneExt
         FROM EmployeeDirectory
         ORDER BY Title ASC, Name ASC
</CFQUERY>

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint