Structured query language (SQL): ORDER BY…ASC | DESC

The idea

ORDER BY sorts the rows a query returns. It comes after SELECT, FROM and any WHERE clause, and names the column to sort on. ASC sorts ascending, from smallest to largest or A to Z; DESC sorts descending, from largest to smallest or Z to A. Without a direction the database usually defaults to ascending, but stating ASC or DESC makes the intended order explicit. Sorting changes only the order of rows, not which rows are returned, so a WHERE filter still decides membership. For example, SELECT Name, Score FROM Results WHERE Score > 50 ORDER BY Score DESC lists qualifying pupils from highest score down. Practise predicting the first and last rows a given ORDER BY produces.

What earns the marks

• ORDER BY sorts the rows returned by a query according to a named column.
• ASC orders ascending (smallest to largest, A to Z); DESC orders descending (largest to smallest, Z to A).
• ORDER BY is placed after SELECT, FROM and any WHERE clause.
• Sorting changes row order only; the WHERE clause still determines which rows appear.
• Naming the direction explicitly avoids relying on a default order.

Common mistakes and how to put them right

• Believing ORDER BY removes rows.

  Put it right: It only reorders the rows already selected, while WHERE controls which rows appear.

• Swapping the meanings of ASC and DESC.

  Put it right: ASC goes up (A to Z, low to high) and DESC goes down (Z to A, high to low).

• Placing ORDER BY before WHERE.

  Put it right: Put the WHERE filter first and the ORDER BY clause last.

Exam tips

• State the first and last row a query would output to prove you understand the chosen direction.
• Check whether the question asks about row order or row membership, and answer accordingly.
• Write the clause order SELECT, FROM, WHERE, ORDER BY when constructing or checking a statement.