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>
Home »
Oracle »
Select » 

Related: