Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

4.5 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.

When you issue a query without an ORDER BY clause, it may sometimes appear that rows come back in the order in which they were originally inserted or in some order matching an index. Don't be fooled. And don't count on such behavior. Unless you write an ORDER BY clause to specify a sort order, you have no guarantee as to the order in which rows are returned.