ORDER BY Clause

Sometimes it is necessary that the rows returned from your query to be in a specific order. For example, I might want scores from high to low or names in alphabetical order. By default, the database will sort data ascending, smallest to largest. Words are sorted alphabetically. NULLs cannot be sorted, they will be listed as found at the bottom of the results.

The Oracle ORDER BY clause must be used if you require that the rows be sorted. The Oracle database, by default, will not sort the results set, nor does it store rows in a table in a specific order.

The only way to insure that the rows are ordered is to use the Oracle ORDER BY clause. Also, sorting is always performed last. It makes no sense to sort an intermediate row set that we will never see.

The ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the ORDER BY clause, the sort order is defaulted to ASC or ascending order.

Some related examples.

1. Ordering data in the SELECT statement ascending.

SQL> SELECT ID FROM Employee ORDER BY Last_Name ASC;

ID
----
05
08
06
07
01
02
04
03

8 rows selected.

2. Query a number type column with order by desc.

SQL> select last_name, salary
  2        from employees
  3       where salary > 12000
  4       order by salary desc
  5      /

LAST_NAME                                              SALARY
---------------------------------------------------- ----------
Lawson                                                  30000
Kyte                                                    25000
Bliss                                                   24000
Oracle                                                  20000
Viper                                                   20000
Beck                                                    20000
Java                                                    20000

LAST_NAME                                              SALARY
-------------------------------------------------- ----------
Wells                                                   20000

8 rows selected.

3. ORDER BY clause with more than one column:by the first column, and within that column, orders by the second.

SQL> SELECT ID FROM Employee
  2  ORDER BY Salary, City;

ID
----
01
07
06
05
08
03
02
04

8 rows selected.

4. Specify ascending or descending for each column.

SQL> SELECT ID, First_Name, Last_Name FROM Employee
  2  ORDER BY Salary ASC, Description DESC;

ID   FIRST_NAME LAST_NAME
---- ---------- ----------
01   Jason      Martin
02   Alison     Mathews
03   James      Smith
08   James      Cat
05   Robert     Black
06   Linda      Green
07   David      Larry
04   Celia      Rice

8 rows selected.

5. Query specific columns with order by clause.

SQL> select job_id, salary
  2  from employees
  3  order by job_id;

JOB_ID                             SALARY
------------------------------ ----------
DBA                                 20000
DBA                                 20000
MGR                                 30000
MGR                                 25000
PROG                                20000
PROG                                20000
PROG                                20000

JOB_ID                             SALARY
------------------------------ ----------
PROG                                24000

8 rows selected.

6. Use aggregate function in order by clause.

SQL> select job_id, avg(salary) avg_salary
  2        from employees
  3       group by job_id
  4       order by avg(salary) desc
  5      /

JOB_ID                         AVG_SALARY
------------------------------ ----------
MGR                                 27500
PROG                                21000
DBA                                 20000

3 rows selected.

7. Order by 3, 2, 1.

SQL> select last_name, email, hire_date
  2  from employees
  3  order by 3, 2, 1
  4  /

LAST_NAME                     EMAIL               HIRE_DATE
--------------------------- ------------------------------ --
Kyte                         tkyte@a.com           13-JUN-98
Bliss                        bliss@g.com           01-JAN-02
Lawson                       lawson@g.com          01-JAN-02
Wells                        wells@g.com           01-JAN-02
Beck                         clbeck@g.com          10-JUN-08
Java                         java01@g.com          10-JUN-08
Viper                        sdillon@a .com        10-JUN-08

LAST_NAME                      EMAIL              HIRE_DATE
-------------------------- ------------------------------ -
Oracle                       oracle1@g.com         10-JUN-08

8 rows selected.

8. Get order number by using rownum column.

SQL> SELECT ROWNUM, ename
  2  FROM   emp;

    ROWNUM ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
         4 JONES
         5 MARTIN
         6 BLAKE
         7 CLARK
         8 SCOTT

8 rows selected.