ORDER BY

ORDER BY sorts the result rows - by salary, by name, by hire date. Without it, DB2 returns rows in whatever order is fastest, which can change between runs.

ORDER BY basics

• ORDER BY comes last in the SELECT statement.
• ORDER BY SALARY sorts ascending (smallest first) - ASC is the default.
• ORDER BY SALARY DESC sorts descending (largest first).
• You can sort by column name, by select-list position (ORDER BY 3), or by an expression.

Sorting by multiple columns

• ORDER BY WORKDEPT, SALARY DESC sorts by department, then salary within each department.
• Each column gets its own direction: ORDER BY WORKDEPT ASC, SALARY DESC.
• Earlier columns dominate - the second column only breaks ties.
• Sort keys can include columns not in the SELECT list.

NULLs and sort order

• By default DB2 treats NULL as the highest value: last in ASC, first in DESC.
• ORDER BY COMM NULLS FIRST / NULLS LAST overrides the default explicitly.
• Character sorting follows EBCDIC order on the mainframe.
• Sorting big results is expensive - sort only what the report needs.

Example