Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Sorting Query Results

Unless you specify otherwise, query results will come back in whatever random order the database happens to retrieve them. To sort the results from a SELECT, use the ORDER BY clause as shown in Example 4-27.

Example 4-27. Sorting query results

               SELECT e.employee_id "ID", e.employee_name "Name", 
                      e.employee_hire_date "Hire Date"
               FROM employee e
               ORDER BY EXTRACT(YEAR FROM employee_hire_date) DESC, employee_name ASC;

        ID Name                                     Hire Date
---------- ---------------------------------------- ---------
       110 Ivan Mazepa                              04-APR-04
       107 Lesia Ukrainka                           02-JAN-04
       113 Mykhailo Verbytsky                       03-MAR-04
       105 Mykola Leontovych                        15-JUN-04
       116 Roxolana Lisovsky                        03-JUN-04
       108 Pavlo Chubynsky                          01-MAR-94
       104 Pavlo Virsky                             29-DEC-87
       111 Taras Shevchenko                         23-AUG-76
       102 Mykhailo Hrushevsky                      16-SEP-64
       112 Igor Sikorsky                            15-NOV-61
       101 Marusia Churai                           15-NOV-61

The ORDER BY clause in Example 4-27 does the following:

EXTRACT(YEAR FROM employee_hire_date) DESC

Sorts initially on the year in which an employee was hired, listing the most recent year first. The EXTRACT function in this case returns the four-digit year as a numeric value. The DESC keyword requests a descending sort.

employee_name ASC

Sorts secondly by employee name. The keyword ASC requests an ascending sort.

The end result is that employees are sorted in descending order by year of hire, and within each year they are further sorted in ascending order by name. The ASC keyword is optional and is rarely used in practice.

Example 4-27 also demonstrates ...