15 Things You Should Know about the ORDER BY Clause

When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right.

The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default.

ORDER BY ASC places NULL values at the end of the query results. ORDER BY DESC places null values at the start of the query results.

The default placement of NULLs with ORDER BY can be changed with the addition of NULLS FIRST/NULLS LAST to the ORDER BY clause.

It is a myth that DISTINCT and GROUP BY will sort data so ORDER BY need not be specified. Without an ORDER BY clause, there is no guarantee that the same query executed again will retrieve rows in the same order. Do not rely on it even if Oracle appears to sort the data. If you want order, use ORDER BY.

If your query is a hierarchical query, do not use ORDER BY as that will destroy the hierarchical order of the CONNECT BY results. To order rows under the same parent, use the ORDER SIBLINGS BY clause.

Instead of column names in the SELECT list, column positions or aliases can be specified to order rows. The position value must be an integer.

SQL> select employee_id, email
  2  from employees
  3  where department_id = 100
  4  -- order by email i.e. position #2
  5  order by 2;

EMPLOYEE_ID EMAIL
----------- -------------------------
        109 DFAVIET
        111 ISCIARRA
        110 JCHEN
        112 JMURMAN
        113 LPOPP
        108 NGREENBE

6 rows selected.

In compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, must specify positions or aliases rather than explicit expressions and must appear only in the last component query. Also, the ORDER BY clause sorts the entire set of rows returned by the compound query.

If the DISTINCT operator appears in the SELECT statement, then ORDER BY cannot refer to columns unless they appear in the SELECT list. ORDER BY in a query without the DISTINCT operator can refer to columns outside the SELECT list.

SQL> -- without DISTINCT
SQL> select manager_id
  2  from employees
  3  where rownum < 10
  4  order by department_id;

MANAGER_ID
----------
       103
       103
       103
       103
       102
       100
       100

       101

9 rows selected.

SQL> -- with DISTINCT
SQL> select distinct manager_id
  2  from employees
  3  where rownum < 10
  4  order by department_id;
order by department_id
         *
ERROR at line 4:
ORA-01791: not a SELECTed expression

The ORDER BY clause can contain a maximum of 255 columns/expressions.

You cannot order by an LOB column, nested table, or varray.

SQL> create type varray_test
  2  as varray(5) of varchar2(30)
  3  /

Type created.

SQL> create table tbl_vt
  2  (
  3    id number
  4   ,vcol  varray_test
  5  )
  6  /

Table created.

SQL> select id
  2  from tbl_vt
  3  order by vcol
  4  /
order by vcol
           *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected - got HR.VARRAY_TEST

You can use expressions in the ORDER BY clause for complex sorting requirements.

e.g. In the table temp_tbl shown below, lets say we want values of condcol = 2 to appear first, then condcol = 1 and then condcol = 3. We can achieve this with a DECODE expression.

SQL> select *
  2  from temp_tbl;

C      VALUE
- ----------
1          1
1          2
2          3
2          4
3          5
3          6

SQL> select *
  2  from temp_tbl
  3  order by decode(condcol,'2',1, 2)
  4         , condcol;

C      VALUE
- ----------
2          3
2          4
1          2
1          1
3          6
3          5

6 rows selected.

ROWNUM is assigned to rows as they satisfy the predicate before the ORDER BY clause is applied. So ROWNUM values do not necessarily come out sequentially in a query with ORDER BY clause.

SQL> select rownum, first_name
  2  from employees
  3  where rownum < 5
  4  order by first_name;

    ROWNUM FIRST_NAME
---------- -------------------
         4 David
         1 Ellen
         3 Mozhe
         2 Sundar

To assign ROWNUM values after rows get sorted by ORDER BY, use an inline view:

SQL> select rownum, t.*
  2  from
  3  (
  4    select first_name
  5    from employees
  6    where rownum < 5
  7    order by first_name
  8  ) t;

    ROWNUM FIRST_NAME
---------- ---------------
         1 David
         2 Ellen
         3 Mozhe
         4 Sundar

To display rows in random order every time a SQL is executed, use order by dbms_random.value.