Back to articles list February 9, 2021 - 6 minutes read What Does ORDER BY Do? Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql ORDER BY 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! Tags: sql learn sql ORDER BY You may also like How ORDER BY and NULL Work Together in SQL Learn how NULLs are sorted by the ORDER BY clause in different databases and how to change the default behavior. Read more Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more GROUP BY in SQL Explained Need to refresh your knowledge of SQL GROUP BY? Learn how GROUP BY works and when it can be useful. Examples provided. Read more NULL Values and the GROUP BY Clause We've already covered how to use the GROUP BY clause but how does SQL's GROUP BY clause work when NULL values are involved? Find out! Read more Useful SQL Patterns: Matching Nulls by Masking Nulls Today, in the first post of the SQL patterns series, we will consider the match by null pattern. It’ll help you deal with tables containing null values. Read more How to Tackle SQL NULLs: COALESCE function Let's introduce the COALESCE postgresql function. It's inevitable that some data in the database has no value. Find out what then with MySQL. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.