ORDER BY queries

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, an alias, or a 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]...];

If the ASC or DESC keyword is not added in an ORDER BY query, the query results are sorted in ascending order by default.

Examples

Single-field sorting

• Display employee names in ascending order of deptno.

  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 in descending order of deptno.

  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 in ascending order of deptno and descending order of sal.

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

Set aliases for the empname, deptno, and sal columns, and sort the employee information in ascending order of department No. and descending order of salary.

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.

Example:

• Display the employee information in ascending order of deptno and descending order of the results of the YEAR(hiredate) function.

  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
  

  In this example, aliases in the ORDER BY clause are used for sorting. YEAR() is a time function that returns the year part of a date.

• Set aliases for the empname, deptno, and YEAR (hiredate) columns, and display the employee information in ascending order of deptno and descending order of Year of birth.

  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.

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