Using the ORDER BY Clause to Sort the Rows

The results from a query are not arranged in any particular order. For example, Figure 8 and Figure 18 appear to be in random order.

You can add an ORDER BY clause to your SELECT statement to direct the system to sort the data in a specific order. The ORDER BY clause is a list of column names from any remote or local table or view. Any expressions that are allowed in the Projection list are allowed in the ORDER BY list. If a column used in the ORDER BY list has a select trigger on it, the trigger will not be activated.

Figure 19 returns every row from the manu_code, manu_name, and lead_time columns in the manufact table, sorted according to lead_time.

Figure 19. Query

SELECT manu_code, manu_name, lead_time
   FROM manufact
   ORDER BY lead_time

You do not need to include the columns that you want to use in the ORDER BY clause in the projection list. That is, you can sort the data according to a column that is not retrieved in the projection list. Figure 20 returns every row from the manu_code and manu_name columns in the manufact table, sorted according to lead_time. The lead_time column is in the ORDER BY clause although it is not declared in the SELECT list.

Figure 20. Query

SELECT manu_code, manu_name,
   FROM manufact
   ORDER BY lead_time

Ascending Order

The retrieved data is sorted and displayed, by default, in ascending order. In the ASCII character set, ascending order is uppercase A to lowercase z for character data types, and lowest to highest value for numeric data types. DATE and DATETIME data is sorted from earliest to latest, and INTERVAL data is ordered from shortest to longest span of time.

Descending Order

Descending order is the opposite of ascending order, from lowercase z to uppercase A for character types and highest to lowest for numeric data types. DATE and DATETIME data is sorted from latest to earliest, and INTERVAL data is ordered from longest to shortest span of time. Figure 21 shows an example of descending order.

Figure 21. Query

SELECT * FROM manufact ORDER BY lead_time DESC

The keyword DESC following a column name causes the retrieved data to be sorted in descending order, as Figure 22 shows.

Figure 22. Query Result

manu_code manu_name       lead_time

 SHM       Shimara           30
 KAR       Karsten           21
 PRC       ProCycle           9
 NKL       Nikolus            8
 NRG       Norge              7
 HSK       Husky              5
 ANZ       Anza               5
 HRO       Hero               4
 SMT       Smith              3

You can specify any column of a built-in data type (except TEXT, BYTE, BLOB, or CLOB) in the ORDER BY clause, and the database server sorts the data based on the values in that column.

Sorting on Multiple Columns

You can also ORDER BY two or more columns, which creates a nested sort. The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence.

Figure 23 and Figure 25 and corresponding query results show nested sorts. To modify the order in which selected data is displayed, change the order of the two columns that are named in the ORDER BY clause.

Figure 23. Query

SELECT stocknum, manu_code, description, unit_price
   FROM stock 
   ORDER BY manu_code, unit_price

In Figure 24, the manu_code column data appears in alphabetical order and, within each set of rows with the same manu_code (for example, ANZ, HRO), the unit_price is listed in ascending order.

Figure 24. Query Result

stock_num manu_code description     unit_price

        5 ANZ       tennis racquet      $19.80 
        9 ANZ       volleyball net      $20.00
        6 ANZ       tennis ball         $48.00
      313 ANZ       swim cap            $60.00
      201 ANZ       golf shoes          $75.00
      310 ANZ       kick board          $84.00
     
·
·
·

      111 SHM       10-spd, assmbld    $499.99
      112 SHM       12-spd, assmbld    $549.00
      113 SHM       18-spd, assmbld    $685.90
        5 SMT       tennis racquet      $25.00
        6 SMT       tennis ball         $36.00
        1 SMT       baseball gloves    $450.00

Figure 25. Query

SELECT stock_num, manu_code, description, unit_price
FROM stock 
ORDER BY unit_price, manu_code

In Figure 26, the data appears in ascending order of unit_price and, where two or more rows have the same unit_price (for example, $20.00, $48.00, $312.00), the manu_code is in alphabetical order.

Figure 26. Query Result

stock_num manu_code description     unit_price

      302 HRO       ice pack             $4.50 
      302 KAR       ice pack             $5.00
        5 ANZ       tennis racquet      $19.80
        9 ANZ       volleyball net      $20.00
      103 PRC       frnt derailleur     $20.00
      
·
·
·

      108 SHM       crankset            $45.00
        6 ANZ       tennis ball         $48.00
      305 HRO       first-aid kit       $48.00
      303 PRC       socks               $48.00
      311 SHM       water gloves        $48.00
      
·
·
·

      113 SHM       18-spd, assmbld    $685.90
        1 HSK       baseball gloves    $800.00
        8 ANZ       volleyball         $840.00 
        4 HSK       football           $960.00

The order of the columns in the ORDER BY clause is important, and so is the position of the DESC keyword. Although the statements in Figure 27 contain the same components in the ORDER BY clause, each produces a different result (not shown).

Figure 27. Query

SELECT * FROM stock ORDER BY manu_code, unit_price DESC

SELECT * FROM stock ORDER BY unit_price, manu_code DESC

SELECT * FROM stock ORDER BY manu_code DESC, unit_price 

SELECT * FROM stock ORDER BY unit_price DESC, manu_code