8. SQL Statements : SELECT (interactive) : Select Statement Clauses : ORDER BY Clause
 
Share this page                  
ORDER BY Clause
The ORDER BY clause allows you to specify the columns on which the results table is to be sorted. For example, if the employees table contains the following data:
ename
edept
emanager
Murtagh
Shipping
Myron
Obidinski
Lumber
Myron
Reilly
Finance
Costello
Barth
Lumber
Myron
Karol
Editorial
Costello
Smith
Shipping
Myron
Loram
Editorial
Costello
Delore
Finance
Costello
Kugel
food prep
Snowden
then this query:
SELECT emanager, ename, edept FROM employees
ORDER BY emanager, edept, ename
produces the following list of managers, the departments they manage, and the employees in each department:
Manager
Department
Employee
Costello
Editorial
Karol
Costello
Editorial
Loram
Costello
Finance
Delore
Costello
Finance
Reilly
Myron
Lumber
Barth
Myron
Lumber
Obidinski
Myron
Shipping
Murtagh
Myron
Shipping
Smith
Snowden
food prep
Kugel
and this query:
SELECT ename, edept, emanager FROM employees
ORDER BY ename
produces this alphabetized employee list:
Employee
Department
Manager
Barth
Lumber
Myron
Delore
Finance
Costello
Karol
Editorial
Costello
Kugel
food prep
Snowden
Loram
Editorial
Costello
Murtagh
Shipping
Myron
Obidinski
Lumber
Myron
Reilly
Finance
Costello
Smith
Shipping
Myron
 
To display result columns sorted in descending order (reverse numeric or alphabetic order), specify ORDER BY column_name DESC. For example, to display the employees in each department from oldest to youngest:
SELECT edept, ename, eage FROM employees
ORDER BY edept, eage DESC;
If a nullable column is specified in the ORDER BY clause, nulls are sorted to the end of the results table.
Note:  Notes:
If the ORDER BY clause is omitted, the order of the rows in the results table is not guaranteed to have any relationship to the storage structure or key structure of the source tables.
There may be occasions where a column name is repeated in the output of an SQL statement, for example, where the same column name is repeated over several tables used in a join. The parser does not generate an error where such an ambiguity exists.
In such cases, we recommend that you clarify your intent by qualifying the column used in the ORDER BY clause by using its table name as a prefix.
In union selects, the result column names must either be the column names from the SELECT clause of the first SELECT statement, or the number of the result column. For example:
SELECT dcolumn FROM dtest
UNION ALL
SELECT zcolumn FROM ztest
ORDER BY dcolumn
In addition to specifying individual column names as the ordering-expressions of the ORDER BY clause, the results table can also be sorted on the value of an expression.
For example, the query:
SELECT ename, edept, emanager FROM employees
       ORDER BY emanager+edept
produces the employee list ordered on the concatenation of the emanager and edept values.
ename
edept
emanager
Loram
Editorial
Costello
Karol
Editorial
Costello
Delore
Finance
Costello
Reilly
Finance
Costello
Murtagh
Shipping
Myron
Obidinski
Lumber
Myron
Barth
Lumber
Myron
Smith
Shipping
Myron
Kugel
food prep
Snowden
 
ORDER BY BOOLEAN results in grouping rows in the order: FALSE, TRUE, NULL unless other modifiers are applied.