Lesson 4 of 17 · First Queries
Sorting With ORDER BY
Sort SQL results with ORDER BY. Learn ascending and descending order, sorting by several columns and where ORDER BY belongs in a query.
- 7 min read
- Beginner
- Reviewed
- How to sort a result from smallest to largest, or the reverse
- How to sort by more than one column
- The order in which the parts of a query must be written
Rows have no guaranteed order
A database does not promise to return rows in any particular order. They often come back in the order they were added, but you must never rely on that. If the order matters, say so with ORDER BY.
SELECT name, age FROM students ORDER BY age;
The youngest student is now first. Sorting from small to large is called ascending order, and it is the default.
Descending order
Add DESC to reverse the order: largest first.
SELECT name, age FROM students ORDER BY age DESC;
You can write ASC for ascending if you want to be explicit, but most people leave it out.
Sorting text and dates
Text is sorted alphabetically, and dates from earliest to latest.
SELECT name, joined FROM students ORDER BY name;
SELECT name, joined FROM students ORDER BY joined DESC;
The second query shows the newest students first, a very common need: latest orders, most recent messages, newest sign-ups.
Sorting by more than one column
List several columns, separated by commas. The database sorts by the first one. The second is used only to decide the order of rows that are equal on the first.
SELECT title, category, hours FROM courses ORDER BY hours DESC, category;
Courses are ordered by length. Where two courses have the same number of hours, they appear in alphabetical order of category. Each column gets its own direction, so DESC here applies only to hours.
Sorting by a calculated column
You can sort by an alias you created in SELECT.
SELECT title, hours * 60 AS minutes FROM courses ORDER BY minutes;
WHERE and ORDER BY together
Filter first, then sort. The clauses must be written in this order.
SELECT name, country, age FROM students WHERE age < 26 ORDER BY age DESC;
So far, the parts of a query go in this order:
SELECTwhich columnsFROMwhich tableWHEREwhich rowsORDER BYin what order
Writing them in a different order is a syntax error.
Common mistakes
List every student's name, country and age, with the oldest student at the top.
-- Show the name, country and age of all students, -- oldest first
In ORDER BY country, age DESC what does DESC apply to?
Summary
- Without
ORDER BY, the order of rows is not guaranteed. ORDER BY columnsorts ascending. AddDESCfor descending.- List several columns to break ties.
- The order of clauses is
SELECT,FROM,WHERE,ORDER BY.
Next: limiting how many rows come back.
Finished reading? Tick it off to track your progress.