1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL ORDER BY Clause: Sorting the Query Result

Discussion in 'SQL PL/SQL' started by SBH, Oct 14, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Objective

    The article explains the use of ORDER BY clause in SELECT statement for sorting the query result set.

    1. The ORDER BY Clause

    Ordering of data in unified direction is known as Sorting. In Oracle, it is achieved by ORDER BY clause, which follows WHERE clause in SELECT queries. Syntax

    Code (Text):
    SELECT <column_name>
    FROM <table_name>
    ORDER BY <column_name>
    Sorting is typically based on single/multiple column specified along with the ORDER BY clause. The possible directional flow can be ascending or descending order of a column value. For example,

    Example 1.1. Below SQL orders the employee data by department id

    Code (SQL):
    SELECT
    EMPLOYEE_ID,
    ENAME,
    DEPARTMENT_ID
    FROM EMPLOYEE
    ORDER BY DEPARTMENT_ID
    Below is the pictorial description of the above picture. First result set shows the unsorted employee records. Once the sorting has been done based on the Department id column in ascending order, second result set shows the sorted records.


    [​IMG]

    Ascending is the default order sorting, if no direction has been specified. Above query lists all employees sorted by their department ids in ascending order (‘ASC’). It can be manually specified by adding keyword ASC to the ORDER BY Clause.

    Sort flow can be changed by specifying ‘DESC’ after the column name as ORDER BY DEPARTMENT_ID DESC.

    2. Notes on ORDER BY Clause usage

    • Order is independent of the column list in SELECT clause, if not the case of positional ordering. This implies that ordering can be based on an unselected column also. For example,

      Example 2.1. Below SQL sorts the result in descending order of Department id, which does not appears in the selected column list

      Code (SQL):
      SELECT  ENAME, SALARY, JOB_ID
      FROM EMPLOYEE
      ORDER BY DEPARTMENT_ID DESC
    • Columns/Expressions involving Column alias can be used in the ORDER BY clause, if specified in the SQL query.
    • Composite Column sorting – Ordering can be based on multiple columns. Different order flow can be specified for each column. In such cases, ordering takes place from right to left. As in the below query, result set is first order by ‘salary’ in ascending order, then y hire date in descending order, if possible.

      Example 2.2. Below SQL orders the result set in ascending order of SALARY and within that set, re-orders on basis of HIRE DATE in descending order

      Code (SQL):
      SELECT
      ENAME,
      LAST_NAME,
      SALARY,
      HIRE_DATE
      FROM EMPLOYEE
      WHERE JOB_ID IN ('SA_REP','MK_MAN')
      ORDER BY SALARY, HIRE_DATE DESC;
    • Positional Sorting - Numeric position of the column in the selected column list can be given in ORDER BY clause, instead of column name. It is mainly used in UNION queries. Positional sorting can be based on multiple columnms.

      Example 2.3. Below SQL orders the result set based on 2nd column of the selected column list i.e SALARY in this case.

      Code (SQL):
      SELECT
      LAST_NAME,
      SALARY
      FROM EMP
      ORDER BY 2;

    3. Handling NULL data using ORDER BY clause

    If the ORDER BY clause is based on a column, which contains NULL data, it will sort to the bottom for ascending order or to the top for descending order. This implies that NULL values appear larger and above than other values.

    This effect can be controlled by the developer by setting the position of NULL data in the result set. NULLS FIRST and NULLS LAST are used to specify the NULL data position. These keywords were introduced in Oracle 8i and produce major effects when dealing with reports. For example,

    Syntax
    Code (Text):
    ORDER BY [column] [NULLS LAST | NULLS FIRST]
    Example 3.1. Below SELECT Statement orders the employees data in descending order of SALARY, keeping NULLs at last.

    Code (SQL):

    SELECT EMPNO,
      ENAME,
      SALARY
    FROM EMPLOYEE
    ORDER BY SALARY DESC NULLS LAST

    EMPNO      ENAME      SALARY
    ---------- ---------- -----------
           100 JOHN            58000
           120 MILLS           53000
           130 KATES          32000
           110 JODE            29000
           140 NEO                
           150 RYAN                
           160 ROBB                
    Prior to Oracle 8i, the same result was achieved by using NVL function and providing alternative value to the ordering column value. Refer the below example.

    Code (Text):
    ORDER BY NVL(commission_pct, -1);
    This gives alternate value to NULL data in the ordering column as -1, and they appear at the bottom of the result set. Please note that this NVL only handles the ordering of data, not the visibility. In the result set, NULLs will appear as NULL only and not as -1.


    References
     

    Attached Files: