ORDER BY clause

 

The ORDER BY clause specifies the particular order in which you want selected rows returned. The order is sorted by ascending or descending collating sequence of a column's or an expression's value.

For example, to retrieve the names and department numbers of female employees listed in the alphanumeric order of their department numbers, you can use this select-statement:

SELECT LASTNAME,WORKDEPT        FROM CORPDATA.EMPLOYEE        WHERE SEX='F'
       ORDER BY WORKDEPT

The result follows.

LASTNAME	WORKDEPT
HAAS	A00
HEMMINGER	A00
KWAN	C01
QUINTANA	C01
NICHOLLS	C01
NATZ	C01
PIANKA	D11
SCOUTTEN	D11
LUTZ	D11
JOHN	D11
PULASKI	D21
JOHNSON	D21
PEREZ	D21
HENDERSON	E11
SCHNEIDER	E11
SETRIGHT	D11
SCHWARTZ	E11
SPRINGER	E11
WONG	E21

Null values are ordered as the highest value.

The column specified in the ORDER BY clause does not need to be included in the SELECT clause. For example, the following statement will return all female employees ordered with the largest salary first:

SELECT LASTNAME,FIRSTNME        FROM CORPDATA.EMPLOYEE        WHERE SEX='F'
       ORDER BY SALARY DESC

If an AS clause is specified to name a result column in the select-list, this name can be specified in the ORDER BY clause. The name specified in the AS clause must be unique in the select-list. For example, to retrieve the full names of employees listed in alphabetic order, you can use this select-statement:

  SELECT LASTNAME CONCAT FIRSTNME AS FULLNAME     FROM CORPDATA.EMPLOYEE     ORDER BY FULLNAME

This select-statement can optionally be written as:

  SELECT LASTNAME CONCAT FIRSTNME     FROM CORPDATA.EMPLOYEE     ORDER BY LASTNAME CONCAT FIRSTNME

Instead of naming the columns to order the results, you can use a number. For example, ORDER BY 3 specifies that you want the results ordered by the third column of the results table, as specified by the select-list. Use a number to order the rows of the results table when the sequencing value is not a named column.

You can also specify whether you want SQL to collate the rows in ascending (ASC) or descending (DESC) sequence. An ascending collating sequence is the default. In the previous select-statement, SQL first returns the row with the lowest FULLNAME expression (alphabetically and numerically), followed by rows with higher values. To order the rows in descending collating sequence based on this name, specify:

… ORDER BY FULLNAME DESC

You can specify a secondary ordering sequence (or several levels of ordering sequences) as well as a primary one. In the previous example, you might want the rows ordered first by department number, and within each department, ordered by employee name. To do this, specify:

… ORDER BY WORKDEPT, FULLNAME

If character columns, or UCS-2 or UTF-16 graphic columns are used in the ORDER BY clause, ordering for these columns is based on the sort sequence in effect when the query is run.

 

Parent topic:

Retrieving data using the SELECT statement

 

Related concepts

Sort sequences and normalization in SQL

 

Related reference

GROUP BY clause