Order 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. See Order columns by 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.

To order by years within job:

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

Produces this report:

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

To order by job within years:

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

Produces this report:

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

Examples:

• List employees in descending order by years of service, and within each year, in descending order by salary:

  SELECT YEARS, ID, NAME, SALARY
  FROM Q.STAFF
  ORDER BY YEARS DESC, SALARY DESC

• List employees in ascending order by salary within department:

  SELECT DEPT, ID, NAME, SALARY
  FROM Q.STAFF
  ORDER BY DEPT, SALARY