ORDER BY

As part of the SQL SELECT statement, you can specify the sequence in which selected rows are displayed. You can also eliminate duplicate rows in a selection.

ORDER BY specifies the order in which rows appear in a report. If you use ORDER BY, it must be the last clause in the entire statement. Any columns named after ORDER BY must also be named after SELECT.

The format of the ORDER BY clause is:
  ORDER BY columnname ASC|DESC

The ASC keyword specifies that you want the data to appear in ascending order; this is the default if no sequence is specified. The DESC keyword specifies that you want the data to appear in descending order.

The following query produces a report with rows in ascending order.

SELECT NAME, JOB, YEARS
FROM Q.STAFF
WHERE DEPT = 84
ORDER BY JOB

Here is the report:

NAME      JOB    YEARS
--------- -----  -----
GAFNEY    CLERK      5
QUILL     MGR       10
DAVIS     SALES      5
EDWARDS   SALES      7

Instead of naming a column for columnname, you can refer to the column by its position in the SELECT statement, which you express as a number.

Sorting sequence

The sequence for sorting character data in numeric order is:

  1. Special characters, including blanks
  2. Lowercase letters in alphabetical order
  3. Uppercase letters in alphabetical order
  4. Numbers
  5. Null values

The default sequence for sorting numbers is ascending order. The default sequence for sorting DATE, TIME, TIMESTAMP, and TIMESTAMP WITH TIME ZONE values is chronological. The sequence for sorting DBCS data is determined by the internal value of the data and generally is not meaningful.

Examples:

  • To list employees in descending order by salary, use a query like the following:
    SELECT ID, NAME, SALARY
    FROM Q.STAFF
    ORDER BY SALARY DESC
  • To list employees in ascending order by last name, use a query like the following:
    SELECT ID, NAME, SALARY
    FROM Q.STAFF
    ORDER BY NAME

Ordering by more than one column

To order by more than one column, put the column name or the column number in a list after ORDER BY. You can mix column names and column numbers in the same list. If you want to order by a defined column, you must use its column number.

A column name in an ORDER BY clause, possibly followed by ASC or DESC, is a sort specification. Sort specifications in a list are separated by commas. The first column that follows the ORDER BY clause is put in order first, the second column is ordered within the limits of the first ORDER BY column, and so on.

Examples:

  • To order by years within job, use a query like the following:
    SELECT NAME, JOB, YEARS
    FROM Q.STAFF
    WHERE DEPT=84
    ORDER BY JOB, YEARS DESC
    This query produces the following report:
    NAME      JOB    YEARS
    --------- -----  -----
    GAFNEY    CLERK      5
    QUILL     MGR       10
    EDWARDS   SALES      7
    DAVIS     SALES      5
  • To order by job within years, use a query like the following:
    SELECT NAME, JOB, YEARS
    FROM Q.STAFF
    WHERE DEPT=84
    ORDER BY YEARS DESC, JOB
    This query produces the following report:
    NAME      JOB    YEARS
    --------- -----  -----
    QUILL     MGR       10
    EDWARDS   SALES      7
    GAFNEY    CLERK      5
    DAVIS     SALES      5
  • To list employees in descending order by years of service and, within each year, in descending order by salary, use a query like the following:
    SELECT YEARS, ID, NAME, SALARY
    FROM Q.STAFF
    ORDER BY YEARS DESC, SALARY DESC
  • To list employees in ascending order by salary within department, use a query like the following:
    SELECT DEPT, ID, NAME, SALARY
    FROM Q.STAFF
    ORDER BY DEPT, SALARY

Ordering columns by column number

You cannot use an expression like SALARY+COMM after an ORDER BY statement. To order by a column defined by an expression, use a number that specifies the column's position in the SELECT statement of the query. For example, consider the following query:
SELECT ID, NAME, SALARY+COMM
FROM Q.STAFF
WHERE COMM IS NOT NULL
ORDER BY 3

In the query above, SALARY+COMM is column 3 in the SELECT statement, so ORDER BY 3 specifies to order by that column.

You can use more than one column number in a list after ORDER BY, and you can use column names and column numbers in the same list. For example, to list employees in descending order by salary within a department, use a query like the following:
SELECT DEPT, ID, NAME, SALARY
FROM Q.STAFF
ORDER BY 1, 4 DESC