8. OpenSQL Statements : Select (interactive) : Order By Clause
 
Share this page                  
Order By Clause
The ORDER BY clause specifies the columns on which the results table is to be sorted. Columns in the ORDER BY clause can be specified using either the column name or a number corresponding to the position of the column in the FROM clause. (You must specify unnamed result columns using a number.) In a union select, use numbers to specify the columns in the ORDER BY clause; column names cannot be used.
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 this 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 (numeric or alphabetic), specify ORDER BY columnname 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 beginning or end of the results table, depending on the host DBMS.
Note:  If the ORDER BY clause is omitted, the order of the rows in the results table is not guaranteed by the DBMS. In particular, the order of the rows in the results table is not guaranteed to have any relationship to the source tables' storage structure or key structure.