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:
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:
and this query:
SELECT emp_name, dept_no, manager FROM employee_dim
ORDER BY enp_name
produces this alphabetized employee list:
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.
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;