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 by
deptnoin ascending order.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 setDisplay employee names by
deptnoin descending order.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 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 |
+---------+--------+------+
| 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. Examples:
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
Sorting by using the NLSSORT function
You can use the NLSSORT function to sort by Chinese Pinyin or stroke number.
Examples
obclient> CREATE TABLE tbl1(col NUMBER,col2 VARCHAR2(20));
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (1,'jab'),(2,'bc'),(3,'rh'),
(4,'nr'),(5,'mh'),(6,'ec');
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1;
+------+------+
| COL | COL2 |
+------+------+
| 1 | A1 |
| 2 | A2 |
| 3 | A3 |
| 4 | A4 |
| 5 | A5 |
| 6 | A6 |
+------+------+
6 rows in set
obclient> SELECT * FROM tbl1 ORDER BY NLSSORT(col2);
+------+------+
| COL | COL2 |
+------+------+
| 2 | bc |
| 6 | ec |
| 1 | jab |
| 5 | mh |
| 4 | nr |
| 3 | rh |
+------+------+
6 rows in set