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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: