Sort the result of an SQL query

Objective

To sort the result of an SQL query using a given column or columns as a key.

Scenario

Suppose that a database has a table for recording information about employees, including their surnames, forenames, dates of birth and salaries:

CREATE TABLE employees (
  surname VARCHAR(40),
  forename VARCHAR(40),
  dob DATE,
  salary INTEGER);

You wish to produce three lists:

• sorted by date of birth (youngest first),
• sorted by salary (highest first), and
• sorted first by surname then (when surnames match) by forename.

(Assuming that everyone has one forename and one surname is a questionable design decision, but it will suffice for this example.)

Method

Add a standard SQL ORDER BY clause to the SELECT statement used to produce the list. Use ASC to specify ascending order and DESC for descending order. To order by date of birth (youngest first):

SELECT surname,forename,dob,salary FROM employees
ORDER BY dob ASC;

To order by salary (highest first):

SELECT surname,forename,dob,salary FROM employees
ORDER BY salary DESC;

To order by surname then forename:

SELECT surname,forename,dob,salary FROM employees
ORDER BY surname ASC,forename ASC;

Variations

Ascending order has been requested explicitly in the examples above, but this is not strictly necessary because ascending order is the default:

SELECT surname,forename,dob,salary FROM employees
ORDER BY dob;

Notes

If the order of two rows has not been constrained by an ORDER BY clause then their order will be chosen arbitrarily and may vary between queries. To avoid this variation you need to either:

• sort on every column (even if you do not greatly care what the order is), or
• ensure that some subset of the columns you sort on is subject to a UNIQUE constraint.

(In the latter case the final sort key listed should form part of the UNIQUE constraint. Once a sufficient number of sort keys have been specified to form a UNIQUE constraint there is no point in specifying any more.)

• Conventions
• Privacy
• Legal