# 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.

 movies id title director production_year 1 Psycho Alfred Hitchcock 1960 2 Midnight in Paris Woody Allen 2011 3 Sweet and Lowdown Woody Allen 1993 4 Talk to Her Pedro Almodóvar 2002 5 The Skin I Live in Pedro Almodóvar 2011

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:

idtitledirectorproduction_year
2Midnight in ParisWoody Allen2011
5The Skin I Live inPedro Almodóvar2011
4Talk to HerPedro Almodóvar2002
3Sweet and LowdownWoody Allen1993
1PsychoAlfred Hitchcock1960

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;
```
idtitledirectorproduction_year
2Midnight in ParisWoody Allen2011
5The Skin I Live inPedro Almodóvar2011
4Talk to HerPedro Almodóvar2002
3Sweet and LowdownWoody Allen1993
1PsychoAlfred Hitchcock1960

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;
```
idtitledirectorproduction_year
1PsychoAlfred Hitchcock1960
5The Skin I Live inPedro Almodóvar2011
4Talk to HerPedro Almodóvar2002
2Midnight in ParisWoody Allen2011
3Sweet and LowdownWoody Allen1993

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:

directornumber_of_movies
Pedro Almodóvar2
Woody Allen2

## 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!