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.

The following query and Figure 3 and the 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 1. Query

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

In the query result, 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 2. 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

The following query shows the reverse order of the columns in the ORDER BY clause.

Figure 3. Query

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

In the query result, 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 4. 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 the following query contain the same components in the ORDER BY clause, each produces a different result (not shown).

Figure 5. 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;