ORDER BY in DB2 SQL

What ORDER BY does

ORDER BY specifies an ordering of the rows of the result table. If you list one sort key, rows are ordered by that key. If you list more than one, Db2 orders by the first key, then breaks ties with the second, then the third, and so on. Ordering follows the same comparison rules as predicates: numbers by magnitude, datetime by chronology, strings by collating sequence after any needed CCSID conversion.

1
2
3
4
SELECT EMPNO, LASTNAME, HIREDATE
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00'
 ORDER BY HIREDATE ASC;

Employees in department A00 come back from earliest hire date to latest. ASC is written here for teaching; you could omit it because ascending is the default. If two people share a hire date, their relative order is arbitrary unless you add another sort key such as LASTNAME or EMPNO. Incomplete ordering is not an error—it is a source of flickering test results.

ORDER BY is applied to the result of the subselect (after WHERE, GROUP BY, HAVING, and the select list). You can sort by a column that is not in the select list in many cases, unless DISTINCT, set operators, or grouping restrict what the sort key may reference. You cannot use a LOB or XML expression as a sort key. Those types are not ordered the way ordinary strings and numbers are.

ASC

ASC means ascending: smaller values first, larger values later. For character data, “smaller” means earlier in the collating sequence, which for EBCDIC is not the same as the ASCII order you see on a laptop. Digits, letters, and special characters sit in different places. If a report “looks unsorted” compared with a spreadsheet, check CCSID and collating sequence before blaming ORDER BY.

Dates and timestamps in ASC run from older to newer. DECIMAL and INTEGER run from more negative (or smaller) to larger. For mixed signed numbers, -100 comes before 5 in ASC. Boolean-looking CHAR(1) flags such as 'N' and 'Y' sort by character, not by English yes/no.

1
2
3
SELECT LASTNAME, SALARY
  FROM HR.EMPLOYEE
 ORDER BY LASTNAME ASC, SALARY ASC;

Last names A–Z (under the active collating sequence), and within the same last name, smaller salary first. Writing ASC twice is optional but documents intent when you mix directions later.

DESC

DESC means descending: larger values first. Use it for “top earners,” “most recent hire,” or “latest timestamp.” Each sort key has its own direction. You can mix ASC and DESC in one ORDER BY.

1
2
3
SELECT EMPNO, LASTNAME, SALARY, HIREDATE
  FROM HR.EMPLOYEE
 ORDER BY SALARY DESC, HIREDATE ASC;

Highest salary first. People with the same salary are then ordered from earliest hire to latest. That mixed pattern is common in ranking reports: primary measure descending, tie-breaker ascending on a stable key.

DESC does not mean “reverse the whole result of an ASC sort” after the fact if you change only one column in a multi-key sort. Only that key’s direction flips. The other keys keep the direction you wrote.

NULL ordering

In Db2 for z/OS query ORDER BY, the null value is higher than all other values. Put that together with ASC and DESC:

• ASC — non-null values in rising order, then nulls at the end
• DESC — nulls first, then non-null values in falling order

1
2
3
SELECT EMPNO, LASTNAME, COMM
  FROM HR.EMPLOYEE
 ORDER BY COMM ASC;

Employees with a commission sort from smallest commission to largest. Employees with COMM NULL appear after every non-null commission. ORDER BY COMM DESC puts the null commissions first, then the largest commission, down to the smallest.

This is a z/OS fact you should not mix up with other platforms. Db2 LUW query ORDER BY supports NULLS FIRST and NULLS LAST so you can override the default. On Db2 for z/OS, those NULLS FIRST / NULLS LAST options appear on the window-order-clause of OLAP specifications (RANK, ROW_NUMBER, and friends), not as a general query ORDER BY clause. If you need nulls at a different end of a report on z/OS, a common pattern is to sort by a CASE expression that maps null to a value you control, then by the real column:

1
2
3
4
5
-- Null commissions first in an otherwise ascending report
SELECT EMPNO, LASTNAME, COMM
  FROM HR.EMPLOYEE
 ORDER BY CASE WHEN COMM IS NULL THEN 0 ELSE 1 END,
          COMM ASC;

The CASE key is 0 for nulls and 1 for everyone else, so nulls sort first even though COMM itself is still “high.” Then COMM ASC orders the non-null commissions. Later pages on CASE in ORDER BY go further; this is enough to control null placement without pretending z/OS has LUW’s NULLS FIRST syntax on ordinary SELECT.

Sort keys: names, numbers, and expressions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Expression as the sort key (and as a named result column)
SELECT EMPNO, SALARY, COMM, SALARY + COMM AS TOTAL_COMP
  FROM DSN8C10.EMP
 WHERE SALARY + COMM > 40000
 ORDER BY SALARY + COMM;
 
-- Same idea using the AS name
SELECT EMPNO, (SALARY + BONUS + COMM) AS TOTAL_SAL
  FROM DSN8C10.EMP
 ORDER BY TOTAL_SAL;
 
-- Column number (brittle if the select list changes)
SELECT EMPNO, LASTNAME, HIREDATE
  FROM DSN8C10.EMP
 ORDER BY 3 DESC;

Integer sort keys are 1-based positions in the result table. ORDER BY 3 DESC sorts by the third selected column. They are compact and easy to get wrong when someone inserts a column at the front of the select list. Prefer names or expressions in application SQL; column numbers show up in generated SQL and old examples.

INPUT SEQUENCE can order rows of a table that was produced by a data-change-table reference in the order the rows were inserted. ORDER OF table-designator copies the ordering of a nested fullselect. Both are specialized; everyday queries use column names and expressions.

ORDER BY, FETCH FIRST, and performance

Sorting is real work: Db2 may use a sort (work files, sort pool) unless an index already delivers the requested order. An ORDER BY on the leading columns of an index, with matching ASC/DESC, is the usual way to avoid a sort. FETCH FIRST n ROWS ONLY plus ORDER BY is the standard “top-n” pattern: sort (or use an index) and stop after n rows. OFFSET and FETCH appear on later pages.

Do not ORDER BY columns you do not need “just in case.” Extra keys widen the sort and can block sort avoidance. Do add a unique tie-breaker (EMPNO, a timestamp) when a report must be stable across runs.

Explain It Like I'm Five

ORDER BY is lining up kids by height. ASC is shortest to tallest. DESC is tallest to shortest. If two kids are the same height, you pick a second rule, like lining them up by name. Kids without a height sticker (null) are treated as “taller than everyone” on z/OS, so they go to the tall end: last in the short-to-tall line, first in the tall-to-short line. If you never say how to line up, the teacher may send kids out in any order.

Exercises

1. List EMPNO, LASTNAME, and SALARY for department D11, highest salary first, then LASTNAME ascending as a tie-breaker.
2. Predict where null COMM values appear for ORDER BY COMM ASC and ORDER BY COMM DESC on Db2 for z/OS.
3. Rewrite ORDER BY 2 using a column name. Why is the name safer?
4. Write an ORDER BY that uses the AS alias TOTAL_PAY for SALARY + BONUS + COMM.
5. Explain why a SELECT without ORDER BY can return a different sequence after an index is dropped, even though the same rows qualify.