Sorting Rows with ORDER BY Clause
The ORDER BY
clause sorts the rows.
The ORDER BY
clause may specify one or more columns on which to sort the data.
The ORDER BY
clause must follow the FROM
clause or
the WHERE
clause if a WHERE
clause is supplied.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
HIREDATE DATE);
INSERT INTO EMP VALUES (1, NULL, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD', TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
SQL> SELECT *
2 FROM emp
3 ORDER BY ename;
EMPNO ENAME HIREDATE
---------- ---------- ---------
2 ALLEN 20-FEB-81
4 JONES 02-APR-81
5 MARTIN 28-SEP-81
3 WARD 22-FEB-81
1 17-DEC-80
SQL>
DESC and ASC
By default, ORDER BY
sorts the columns in ascending order: lower values appear first.
You can use the DESC
keyword to sort the columns in descending order: higher values appear first.
You can use the ASC
keyword to explicitly specify an ascending sort.
SQL> SELECT *
2 FROM emp
3 ORDER BY ename ASC, hiredate DESC;
EMPNO ENAME HIREDATE
---------- ---------- ---------
2 ALLEN 20-FEB-81
4 JONES 02-APR-81
5 MARTIN 28-SEP-81
3 WARD 22-FEB-81
1 17-DEC-80
SQL>
Column Index
Using a column position number in the ORDER BY
clause to indicate which column to sort.
Using 1 to sort by the first column selected, 2 to sort by the second column selected, and so on.
In the following query, column 1 (the ename column) is used to sort the rows:
SQL> SELECT ename, empno FROM emp
2 ORDER BY 1;
ENAME EMPNO
---------- ----------
ALLEN 2
JONES 4
MARTIN 5
WARD 3
1
SQL>
ORDER BY and NULL
When you sort by a numeric datatype column, and that column contains NULL
values,
the NULL
values will sort as being greater than all NOT NULL
values.
The same is true for character data types and date data types.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
HIREDATE DATE);
INSERT INTO EMP VALUES (1, NULL, NULL);
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD', TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
SQL> SELECT *
2 FROM emp
3 ORDER BY ename;
EMPNO ENAME HIREDATE
---------- ---------- ---------
2 ALLEN 20-FEB-81
4 JONES 02-APR-81
5 MARTIN 28-SEP-81
3 WARD 22-FEB-81
1
SQL>
SQL>
SQL> SELECT *
2 FROM emp
3 ORDER BY hiredate;
EMPNO ENAME HIREDATE
---------- ---------- ---------
2 ALLEN 20-FEB-81
3 WARD 22-FEB-81
4 JONES 02-APR-81
5 MARTIN 28-SEP-81
1
SQL>