Velamuvi AIAcademy

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
In this lesson you will learn
  • 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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
SELECT name, joined
FROM students
ORDER BY name;
Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
SELECT name, country, age
FROM students
WHERE age < 26
ORDER BY age DESC;

So far, the parts of a query go in this order:

  1. SELECT which columns
  2. FROM which table
  3. WHERE which rows
  4. ORDER BY in what order

Writing them in a different order is a syntax error.

Common mistakes

Practice exercise

List every student's name, country and age, with the oldest student at the top.

Your solutionSQL
-- Show the name, country and age of all students,
-- oldest first
Knowledge check

In ORDER BY country, age DESC what does DESC apply to?

Summary

  • Without ORDER BY, the order of rows is not guaranteed.
  • ORDER BY column sorts ascending. Add DESC for 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.