ORDER BY

Beginner

When we’re writing a SQL query, we’ll often want to specify the order of the rows in our result set. For example:

if we want to fetch a list of people, sorted by age
if we want to fetch a list of events, sorted by date
if we want to fetch a list of test scores, sorted from highest to lowest (or vice versa)

In SQL, we use the ORDER BY clause to achieve this. By adding an ORDER BY clause onto the end of our query, we can sort our rows in ascending or descending order, by one or more columns.

Sorted in ascending order (default)

Take a look at this simple query:

SELECT *
FROM students
ORDER BY first_name

This query will return all rows from the students table, sorted by first_name in ascending order (the default sort order):

Sorted in descending order

If we wanted to place the results in descending order instead, we’d add the DESC operator just after the column we’re sorting by (in this case, that’s the first_name column):

SELECT *
FROM students
ORDER BY first_name DESC

This query will return all rows from the students table, sorted by first_name in descending order:

Ordering by numbers

We can also sort our results by numeric columns. For example, if we wanted to fetch all the students from the students table, sorted in descending order by age, we’d write:

SELECT *
FROM students
ORDER BY age DESC

Ordering by multiple columns

Sometimes, we’ll want to sort our results by multiple columns. For example, we might want to sort the students first by class, and then, within each class, sort the students by first_name. We could achieve this by writing:

SELECT *
FROM students
ORDER BY class ASC, first_name DESC

This query will return all rows from the students table, sorted first by class in ascending order and then by first_name in descending order. This means that, if there are multiple rows with the same class, those rows will be further sorted by first_name in descending order.

Note: I’ve added the ASC operator to make it clear that the class should be sorted in ascending order. But since ascending order is the default sort order, it’s not strictly necessary to include this operator, and we could just as well write:

SELECT *
FROM students
ORDER BY class, first_name DESC

Practice

Now it's your turn! Open the following questions in a new tab to try out the ORDER BY clause:

Previous Next