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 how column aliases may be enclosed in double quotes to allow for spaces and lowercase letters in alias names. Such names can make query results more readable.