4. SQL Statements : SELECT : 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:
emp_name
dept_no
manager
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 manager, emp_name, dept_no FROM employee_dim
ORDER BY manager, dept_no, emp_name
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 emp_name, dept_no, manager FROM employee_dim
ORDER BY enp_name
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 dept_no, emp_name, emp_age FROM employee_dim
ORDER BY dept_no, emp_age DESC;
If a nullable column is specified in the ORDER BY clause, nulls are sorted to the end of the results table by default. When using the DESC modifier the NULLS are moved to the beginning of the output. To modify this behavior use the NULLS LAST or NULLS FIRST modifier.
SELECT dcolumn FROM dtest
UNION ALL
SELECT zcolumn FROM ztest
ORDER BY dcolumn NULLS FIRST;
SELECT dcolumn FROM dtest
UNION ALL
SELECT zcolumn FROM ztest
ORDER BY dcolumn DESC NULLS LAST;
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 emp_name, dept_no, manager FROM employee_dim
       ORDER BY manager||dept_no
produces the employee list ordered on the concatenation of the manager and dept_no values.
emp_name
dept_no
manager
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.
Note:  The ORDER BY clause must contain a column name or expression that is in the select list.
Incorrect:
SELECT DISTINCT col1, col2 FROM table ORDER BY col3;
Correct:
SELECT DISTINCT col1, col2, col3 FROM table ORDER BY col3;
SELECT DISTINCT emp_name, dept_no, employee_id FROM employee_dim ORDER BY employee_id;