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.

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   |     25 |
  | BLAKE   |     30 |
  | ALLEN   |     35 |
  | JONES   |     40 |
  | FORD    |     45 |
  +---------+--------+
  6 rows in set
  

• Display employee names in descending order of deptno.

  obclient> SELECT empname, deptno FROM emp ORDER BY deptno DESC;
  +---------+--------+
  | EMPNAME | DEPTNO |
  +---------+--------+
  | FORD    |     45 |
  | JONES   |     40 |
  | ALLEN   |     35 |
  | BLAKE   |     30 |
  | SCOTT   |     25 |
  | SMITH   |     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  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| SCOTT   |     25 | 3000 |
| BLAKE   |     30 | 2850 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

Sorting after the WHERE clause

Add ORDER BY after the WHERE clause for sorting. Example:

obclient> SELECT empname, deptno,sal, HIREDATE FROM emp WHERE sal>=1000 ORDER BY HIREDATE;
+---------+--------+------+-----------+
| EMPNAME | DEPTNO | SAL  | HIREDATE  |
+---------+--------+------+-----------+
| ALLEN   |     35 | 1600 | 20-FEB-81 |
| JONES   |     40 | 2975 | 02-APR-81 |
| BLAKE   |     30 | 2850 | 01-MAY-81 |
| FORD    |     45 | 3000 | 05-DEC-81 |
| SCOTT   |     25 | 3000 | 15-JUL-87 |
+---------+--------+------+-----------+
5 rows in set