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 single field, multiple fields, alias, and function. Multiple fields are separated by 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 names 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 descending 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 names 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 and alias 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

Add ORDER BY after the WHERE clause for sorting. Sample code:

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