Ordering the result table rows

If you want to guarantee that the rows in your result table are ordered in a particular way, you must specify the order in the SELECT statement. Otherwise, DB2® can return the rows in any order.

About this task

To retrieve rows in a specific order, use the ORDER BY clause. Using ORDER BY is the only way to guarantee that your rows are ordered as you want them. The following topics show you how to use the ORDER BY clause.

Specifying the sort key in the ORDER BY clause:

The order of the selected rows depends on the sort keys that you identify in the ORDER BY clause. A sort key can be a column name, an integer that represents the number of a column in the result table, or an expression. DB2 orders the rows by the first sort key, followed by the second sort key, and so on.

You can list the rows in ascending or descending order. Null values appear last in an ascending sort and first in a descending sort.

DB2 sorts strings in the collating sequence associated with the encoding scheme of the table. DB2 sorts numbers algebraically and sorts datetime values chronologically.

Restriction: You cannot use the ORDER BY clause with LOB or XML columns.

Example: ORDER BY clause with a column name as the sort key: Retrieve the employee numbers, last names, and hire dates of employees in department A00 in ascending order of hire dates:

SELECT EMPNO, LASTNAME, HIREDATE
   FROM DSN8A10.EMP
   WHERE WORKDEPT = 'A00'
   ORDER BY HIREDATE ASC;

The result table looks similar to the following output:

EMPNO    LASTNAME       HIREDATE
=====    =========      ==========
000110   LUCCHESI       1958-05-16
000120   O'CONNELL      1963-12-05
000010   HAAS           1965-01-01
200010   HEMMINGER      1965-01-01
200120   ORLANDO        1972-05-05

Example: ORDER BY clause with an expression as the sort key: The following subselect retrieves the employee numbers, salaries, commissions, and total compensation (salary plus commission) for employees with a total compensation greater than 40000. Order the results by total compensation:

SELECT EMPNO, SALARY, COMM, SALARY+COMM AS "TOTAL COMP"
   FROM DSN8A10.EMP
   WHERE SALARY+COMM > 40000
   ORDER BY SALARY+COMM;

The intermediate result table looks similar to the following output:

EMPNO   SALARY    COMM      TOTAL COMP
======  ========  =======   ==========
000030  38250.00  3060.00   41310.00
000050  40175.00  3214.00   43389.00
000020  41250.00  3300.00   44550.00
000110  46500.00  3720.00   50220.00
200010  46500.00  4220.00   50720.00
000010  52750.00  4220.00   56970.00

Referencing derived columns in the ORDER BY clause:

If you use the AS clause to name an unnamed column in a SELECT statement, you can use that name in the ORDER BY clause.

Example: ORDER BY clause that uses a derived column: The following SQL statement orders the selected information by total salary:

SELECT EMPNO, (SALARY + BONUS + COMM) AS TOTAL_SAL
   FROM DSN8A10.EMP
   ORDER BY TOTAL_SAL;

Parent topic: Formatting the result table