What Does ORDER BY Do?

When analyzing data, it often helps to have rows ordered in a specific way. In this article, I’ll use multiple examples to show how SQL ORDER BY sorts data according to one or more columns in ascending or descending order.

Introduction to ORDER BY

By default, the order of rows in the output of an SQL query is arbitrary. If you want to sort the output in a particular order, you’ll need to use the ORDER BY keyword. The rows are sorted according to one or more columns specified in the ORDER BY clause. Unless you tell it otherwise, ORDER BY sorts data in ascending order.

Let’s look at an example to understand the syntax of ORDER BY. We have the following table with basic information on several famous movies.

Our first task is to sort these rows by the production year, starting with the earliest movie. With numerical data, ascending order implies sorting from the smallest number to the largest, and descending order means sorting from the largest number to the smallest. Since we want the earliest movie to come first, we need to sort the output in ascending order. This is the default order when using ORDER BY, but we can also specify the order using the ASC keyword.

Here’s how SQL ORDER BY sorts the movie table by production year, in ascending order:

To sort the output with ORDER BY, you simply:

• Put the ORDER BY keyword at the end of the query.
• Follow it with the name of the column that you want to use for sorting.
• Specify the ASC or DESC keyword for ascending or descending order, respectively. This is optional.

In the example above, we sorted the result according to the production year, starting from the earliest movie. Can you now write an SQL query to order the output according to the production year but starting from the most recent movie?

I’ll show the answer below, but now you can test yourself and write an SQL query to get the following output:

And here’s the query:

SELECT *
FROM movies
ORDER BY production_year DESC;

We’ve simply replaced the ASC keyword with the DESC keyword to change the order from ascending to descending. Pretty simple, right?

To get hands-on experience with ORDER BY, check out our interactive SQL Basics course.

Now let’s look into more examples of using ORDER BY in different scenarios.

ORDER By with Text Data

Let’s order the movies table by the movie title in reverse alphabetical order. With text data, ascending order implies sorting in alphabetical order (A-Z) and descending order implies sorting in reverse alphabetical order (Z-A). Thus, our SQL query will be as follows:

SELECT *
FROM movies
ORDER BY title DESC;

We don’t have NULL values in our example, but you can learn how ORDER BY works with NULLs in this article.

ORDER BY Multiple Columns

You may often need a query’s output to be sorted according to several columns. This makes sense when the first column has multiple rows with the same values. Let’s see how this works.

In our next example, we want to order the movies (1) by the director’s name (in alphabetical order), and (2) by the production year (starting with the latest movie). Considering how ORDER BY works with the numerical and text data, we’ll sort the result set by the director column in ascending order and then by production_year in descending order:

SELECT *
FROM movies
ORDER BY director ASC, production_year DESC;

As expected, the result set starts with a movie by Alfred Hitchcock, whose name comes first alphabetically. Then, we have two movies by Pedro Almodóvar, whose name is second according to alphabetical order. The movies of Pedro Almodóvar are then sorted by the production year, with the movie from 2011 going first and the movie from 2002 going second. The table ends with the movies of Woody Allen, also sorted from latest to earliest. That is the order we were looking for.

ORDER BY in Complex SQL Queries

When you have an SQL query with multiple clauses that join, group, and filter data, it’s important to put the ORDER BY clause at the very end of the query.

For example, let’s say we want to get the names of movie directors with more than one movie and we want them ordered alphabetically. Here’s the query to use:

SELECT director, count(title) AS number_of_movies
FROM movies
GROUP BY director
HAVING count(title) > 1
ORDER BY director;

In this query, we first group the data, then filter the groups according to our condition ( > 1 movie), and finally sort the result set according to the director column (in ascending order, by default).

Here’s the result we were looking for:

Wrapping Up SQL ORDER BY

The following rules summarize key concepts for using ORDER BY:

• ORDER BY is always put at the very end of the query. Clauses like FROM, WHERE, GROUP BY, HAVING, etc. should be put before the ORDER BY keyword.
• To sort the output in ascending order, you may put the keyword ASC after the column name. However, that’s optional, since it will sort in ascending by default.
• To sort the output in descending order, put the keyword DESC after the column name.
• To sort the output by multiple columns, simply specify the column names in the ORDER BY The result set will be sorted by the first column; if there are multiple rows with the same value in the first column, the rows will be sorted according to the second column, etc.
• When sorting by multiple columns, you can put the ASC and DESC keywords after each of the column names. In other words, you may sort some columns in ascending order and some columns in descending order within the same query.

These are the basic rules for using the ORDER BY clause. To learn more advanced usages, like putting expressions in ORDER BY, check out this comprehensive guide.

Time to Practice SQL ORDER BY!

You’ve learned how SQL ORDER BY works; now it’s time to put your new knowledge into practice. I recommend starting with the LearnSQL.com SQL Basics course, which covers all fundamental SQL concepts and includes a number of interactive exercises on sorting rows with ORDER BY.

For more exercises, check out our SQL Practice Set. This course tests basic SQL knowledge with multiple exercises on data aggregation, grouping, filtering, and, of course, ordering.

Learning SQL shouldn’t be difficult. Learn with fun!