Sorting with ORDER BY

The rows come back in no particular order. Add ORDER BY to sort them by a column:

SELECT name, score FROM student ORDER BY score DESC;

• ASC sorts ascending (smallest first) — this is the default.
• DESC sorts descending (largest first).

Sort text columns alphabetically:

SELECT name FROM student ORDER BY name;

You can sort by several columns: ORDER BY form ASC, score DESC sorts by form first, then by score within each form. ORDER BY always comes last in the query.

Question. List each student's form, name and score, grouped by form (A to Z), with the highest score first inside each form. If two students in a form tie, list them alphabetically.

Answer. Each extra column in ORDER BY only breaks ties left by the ones before it:

SELECT form, name, score FROM student
ORDER BY form, score DESC, name;

form | name  | score
11A  | Sara  | 95
11A  | Lin   | 88
11A  | Mei   | 88
11B  | Jamal | 72
11B  | Tom   | 64

Lin and Mei both scored 88, so the third key, name, puts Lin first.

• DESC belongs to one column. In ORDER BY form, score DESC only score is descending; form is still ascending.
• WHERE comes before ORDER BY. SELECT name FROM student ORDER BY score WHERE form = '11A' is a syntax error.
• Without ORDER BY, never rely on the order the rows come back in.