15 Things You Should Know about the ORDER BY Clause

September 3, 2012

in faq, keywords, sql

Oracle ORDER BY Clause

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

  2. 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.

  3. 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.

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

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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

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

  11. 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

  12. 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.

  13. 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

  14. 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

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

{ 5 comments… read them below or add one }

Pranali Bhabad March 23, 2014 at 12:31 pm

Thank you so much…I got my answer..

garagonp May 22, 2014 at 10:30 pm

16. You can also use subqueries to order data:

SELECT country_id, city, state_province
FROM locations l
ORDER BY (SELECT country_name
FROM countries c
WHERE l.country_id = c.country_id);

Though I have no idea why would someone need something like that 🙂

Girisha N S July 1, 2014 at 11:55 am

hi,

If i user order by Desc of one column and asc of one column in where clause how it works on this situtaion.

Regards
Girisha N.S

Raghav December 3, 2015 at 2:15 pm

Say I have 10 columns in my SELECT clause.
I chose 3 columns from that to put in my ORDER BY Clause.
If there are duplicate combinations among those 3 columns, it is expected that we cannot guarantee how the rows within that duplicate combination will be retrieved.
In such a case, is there any parameter in Oracle which will help us sort within such combinations also with the remaining columns even though the remaining columns are not explicitly included in ORDER BY clause?
Appreciate your help.
Regards
Raghav

oratabler December 14, 2015 at 12:50 pm

@Raghav: Didn’t really get the problem here. If the remaining columns need to be ordered too, include them in the ORDER BY clause? Why is another parameter required?

Leave a Comment

Previous post:

Next post: