The ORDER BY clause sorts query results by one or multiple attribute columns in ascending or descending order. The query results are sorted in ascending order by default.
Syntax
ORDER BY supports sorting by a single field, multiple fields, alias, and function. Separate multiple fields with commas (,). Syntax for the ORDER BY clause:
SELECT select_list FROM table_list
[WHERE query_condition]
ORDER BY column_name [ASC|DESC] [,column_name [ASC|DESC]...];
Examples
Single-field sorting
Display employee information by deptno in ascending order.
obclient> SELECT empname, deptno FROM emp ORDER BY deptno;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH | 20 |
| SCOTT | 20 |
| ALLEN | 30 |
| BLAKE | 30 |
| JONES | 40 |
| FORD | 40 |
+---------+--------+
6 rows in set
Display employee names by deptno in ascending order.
obclient> SELECT empname, deptno FROM emp ORDER BY deptno DESC;
+---------+--------+
| empname | deptno |
+---------+--------+
| JONES | 40 |
| FORD | 40 |
| ALLEN | 30 |
| BLAKE | 30 |
| SMITH | 20 |
| SCOTT | 20 |
+---------+--------+
6 rows in set
Multiple-field sorting
Display employee information by deptno in ascending order and by sal in descending order.
obclient> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;
+---------+--------+---------+
| empname | deptno | sal |
+---------+--------+---------+
| SCOTT | 20 | 3000.00 |
| SMITH | 20 | 800.00 |
| BLAKE | 30 | 2850.00 |
| ALLEN | 30 | 1600.00 |
| FORD | 40 | 3000.00 |
| JONES | 40 | 2975.00 |
+---------+--------+---------+
6 rows in set
Sorting by alias
obclient> SELECT empname 'Employee name', deptno 'Department No.', sal 'Salary' FROM emp ORDER BY Department No.ASC,Salary DESC;
+--------------+--------------+---------+
| Employee name | Department No. | Salary |
+--------------+--------------+---------+
| SCOTT | 20 | 3000.00 |
| SMITH | 20 | 800.00 |
| BLAKE | 30 | 2850.00 |
| ALLEN | 30 | 1600.00 |
| FORD | 40 | 3000.00 |
| JONES | 40 | 2975.00 |
+--------------+--------------+---------+
6 rows in set
Sorting by functions
Use functions in the ORDER BY clause for sorting. YEAR() is a time function that returns the year part of a date.
obclient> SELECT empname, deptno, YEAR(hiredate) FROM emp ORDER BY deptno ASC, YEAR(hiredate) DESC;
+---------+--------+----------------+
| empname | deptno | YEAR(hiredate) |
+---------+--------+----------------+
| SCOTT | 20 | 1987 |
| SMITH | 20 | 1980 |
| ALLEN | 30 | 1981 |
| BLAKE | 30 | 1981 |
| JONES | 40 | 1981 |
| FORD | 40 | 1981 |
+---------+--------+----------------+
6 rows in set
Use aliases in the ORDER BY clause for sorting. YEAR() is a time function that returns the year part of a date.
obclient> SELECT empname 'Employee name', deptno 'Department No.',YEAR (hiredate) 'Year of birth' FROM emp ORDER BY deptno ASC,Year of birth DESC;
+--------------+--------------+--------------+
| Employee name | Department No. | Year of birth |
+--------------+--------------+--------------+
| SCOTT | 20 | 1987 |
| SMITH | 20 | 1980 |
| ALLEN | 30 | 1981 |
| BLAKE | 30 | 1981 |
| JONES | 40 | 1981 |
| FORD | 40 | 1981 |
+--------------+--------------+--------------+
6 rows in set
Sorting after the WHERE clause
Use the WHERE clause to filter data before sorting. Examples:
obclient> SELECT empname, deptno, sal, YEAR(hiredate) FROM emp WHERE sal >= 1000 ORDER BY YEAR(hiredate);
+---------+--------+---------+----------------+
| empname | deptno | sal | YEAR(hiredate) |
+---------+--------+---------+----------------+
| ALLEN | 30 | 1600.00 | 1981 |
| JONES | 40 | 2975.00 | 1981 |
| BLAKE | 30 | 2850.00 | 1981 |
| FORD | 40 | 3000.00 | 1981 |
| SCOTT | 20 | 3000.00 | 1987 |
+---------+--------+---------+----------------+
5 rows in set