Back to articles list May 13, 2021 - 9 minutes read A Detailed Guide to SQL ORDER BY 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 It’s often necessary to display the output of SQL query in specific order rather than arbitrarily. In this article, I’ll explain the many ways you can do it with the SQL ORDER BY clause. To sort records in SQL, you’ll need to use the ORDER BY clause. In this article, I’ll explain in detail how to use ORDER BY to sort output by one or more columns, in ascending (A-Z) or descending (Z-A) order, and by using existing column(s) or using column(s) calculated by an aggregate function. Don’t worry – it’s not as complicated as it sounds! How to Sort Rows in SQL If you don’t sort the output of your SQL query, the row order will be arbitrary. If you want the rows of the resulting table to appear in specific order, you’ll need to use the ORDER BY clause. The basic syntax for ORDER BY is as follows: SELECT ...columns... FROM table ORDER BY sorting_column ASC/DESC; After the ORDER BY keyword, you simply list the column(s) by which you want to sort the records. These columns do not need to be displayed in the output, i.e., listed in the SELECT statement. You set the sorting order (ascending or descending) using the DESC and ASC keywords. The ASC keyword is optional; if no keyword is specified after the column name, the rows will be sorted in ascending order by default. ORDER BY is positioned at the end of the query. Check out this article for an animation of how ORDER BY works. Now, let’s see some practical ORDER BY use cases. ORDER BY One Column Imagine that we run a shop that sells Halloween costumes. We have different kinds of costumes, masks, hats, and accessories in stock. Here is the table with our products: products idnamecategoryquantityprice 11Wonder Womancostumes219.95 12Vampiremasks43.95 13Magic Wandaccessories62.50 14Wizard of Ozhats33.95 15Supermancostumes314.95 16Hulkcostumes212.50 17Witchhats44.95 18Rapunzelcostumes0NULL 19Snow Whitecostumes123.95 20Freddy Kruegermasks33.95 21Alienmasks0NULL 22Cowboyhats34.95 23Ghostbusteraccessories213.95 24Swordaccessories73.50 25Zombiemasks82.95 Simple ORDER BY: Sorting by One Column Let’s start with a very basic example: ordering our items alphabetically by name. To sort items in alphabetical order, we just need to order our result set by the name column in ascending order. As we know, ascending order is the default in SQL, so the ASC keyword can be omitted. Here’s our query: SELECT * FROM products ORDER BY name; And here’s the result, with all the records sorted alphabetically based on the product name: idnamecategoryquantityprice 21Alienmasks0NULL 22Cowboyhats34.95 20Freddy Kruegermasks33.95 23Ghostbusteraccessories213.95 16Hulkcostumes212.50 13Magic Wandaccessories62.50 18Rapunzelcostumes0NULL 19Snow Whitecostumes123.95 15Supermancostumes314.95 24Swordaccessories73.50 12Vampiremasks43.95 17Witchhats44.95 14Wizard of Ozhats33.95 11Wonder Womancostumes219.95 25Zombiemasks82.95 Pretty simple, right? Now let’s sort our Halloween costumes based on price, starting from the most expensive. Going from highest to the lowest price requires sorting in descending order. Thus, our ORDER BY clause should include the DESC keyword: SELECT * FROM products ORDER BY price DESC; idnamecategoryquantityprice 19Snow Whitecostumes123.95 11Wonder Womancostumes219.95 15Supermancostumes314.95 23Ghostbusteraccessories213.95 16Hulkcostumes212.50 17Witchhats44.95 22Cowboyhats34.95 14Wizard of Ozhats33.95 12Vampiremasks43.95 20Freddy Kruegermasks33.95 24Swordaccessories73.50 25Zombiemasks82.95 13Magic Wandaccessories62.50 21Alienmasks0NULL 18Rapunzelcostumes0NULL Here are several interesting things to note about SQL sorts: Order of rows with the same value. Some items in our table have the same price – specifically, the Witch and Cowboy hats are $4.95, while the Wizard of Oz hat and the Vampire and Freddy Krueger masks are $3.95. How does SQL order such rows? There are actually no specific rules for these cases; the result is non-deterministic. In other words, the order of rows containing the same sort column value may vary every time the query is run. If you need the order of rows to be consistent, use a unique column to sort the rows. If that’s not possible, see if you can sort the result set using a combination of columns that result in a determined order. We’ll be covering sorting by multiple columns later in this article. Sorting behavior for NULL values. In the result table above, rows with NULL values in the sort column are put at the very end of the table. However, this behavior is not consistent across different relational databases. I used MySQL to run this query, and this database considers NULL values as the lowest possible value. Thus, it sorts them last when descending order is requested. The result would be the same in the SQL Server and SQLite databases. However, PostgreSQL and Oracle databases treat NULLs as the largest possible values and put them first when sorting in descending order. In most cases, you can change the default behavior of ORDER BY with respect to NULL values. Learn how in this detailed guide. Great! Let’s move on to more complicated examples. Sorting by a Column Calculated by an Aggregate Function In SQL, you can sort results based on one or more calculated columns. This column can be calculated using one of the aggregated functions (like AVG(), SUM(), COUNT(), MAX(), MIN()) or another method. Let’s see how this works in practice. In our next example, we want to see which product categories include more expensive items. In particular, we want to sort categories by their average product price, starting with the most expensive. We’ll calculate the average price for each category, then sort rows in descending order according to this calculated column: SELECT category, AVG(price) AS avg_price FROM products GROUP BY category ORDER BY avg_price DESC; Note that the ORDER BY clause is placed last, after the GROUP BY clause. Here’s the result: categoryavg_price costumes17.84 accessories6.65 hats4.62 masks3.62 It’s good to note that ORDER BY accepts number notation when referring to the sort column. For example, to sort the result set based on the first column, you can simply write ORDER BY 1. Accordingly, the following query would give us the same result as in the above table: SELECT category, AVG(price) AS avg_price FROM products GROUP BY category ORDER BY 2 DESC; Since the avg_price column is the second in our SELECT statement, we can refer to it in the ORDER BY clause using ‘2’. This approach saves us time on typing, but it has one important drawback. If you modify the query by adding more fields to the SELECT statement, you should remember to check whether the number in the ORDER BY is still relevant or needs to be changed. Unfortunately, this step is quite often overlooked, resulting in the wrong order in the result set. ORDER BY Multiple Columns ORDER BY allows sorting the result set by two or more columns. However, to use this tool appropriately you need to understand how it works: Sorting starts with ordering the data based on the first column mentioned in the ORDER BY If this column contains the same values, the corresponding rows are ordered based on the second column listed in the ORDER BY clause, and so on. Thus, the order of columns matters. You can choose the order (i.e. ascending or descending) for each of the columns listed in ORDER BY. To specify the order, you simply put the DESC or ASC keyword after each of the columns listed. If you omit the DESC / ASC keyword, it will be sorted in ascending order by default. Let’s see some examples. Sorting by Multiple Columns in SQL You may recall that when we were sorting our products according to price, the sorting result was non-deterministic. Specifically, there were several items with the same price that should have appeared in the same position but were actually ordered randomly. If we want more control over the sort order in such cases, we should add another column to the ORDER BY clause. For example, we may start by sorting the items based on their price (from the most expensive to the cheapest). Then we may choose to alphabetically sort items with the same price. This implies ordering by price in descending order and then ordering by name in ascending order. Here’s the query: SELECT id, name, price FROM products ORDER BY price DESC, name; idnameprice 19Snow White23.95 11Wonder Woman19.95 15Superman14.95 23Ghostbuster13.95 16Hulk12.50 22Cowboy4.95 17Witch4.95 20Freddy Krueger3.95 12Vampire3.95 14Wizard of Oz3.95 24Sword3.50 25Zombie2.95 13Magic Wand2.50 21AlienNULL 18RapunzelNULL We have selected only three columns so that we can more easily focus on the sorting results. As you can see: The Cowboy and Witch hats have the same price and are now ordered alphabetically. Similarly, another group of items priced at $3.95 is also ordered alphabetically by name. Finally, SQL considers the NULL values in the price column as the same value, so we have those rows ordered alphabetically according to the item name. You can use the number notation when listing multiple columns in the ORDER BY clause; it works the same way. The following query will give us the same results as the one above: SELECT id, name, price FROM items ORDER BY 3 DESC, 2; However, you should be very careful with number notation, since editing the query may change the column order – and thus the sort output. Sorting by Multiple Calculated Columns Let’s add complexity to our final sorting example. In this case, we want to sort the product categories by the total number of in-stock items, starting from the category with the fewest items. If any of the categories have the same number of items in stock, we want the category with the highest product price to be listed first. In other words, we want to sort the output by two columns, with both calculated using aggregate functions. Moreover: The sum of the number of items in each category should be sorted in ascending order. The maximum product price in each category should be sorted in descending order. Here’s the query and the result: SELECT category, SUM(quantity) AS total_quantity, MAX(price) AS max_price FROM items GROUP BY category ORDER BY total_quantity, max_price DESC; categorytotal_quantitymax_price costumes823.95 hats104.95 accessories1513.95 masks153.95 In this query, we calculate the total number of items (SUM(quantity)) and the maximum product price (MAX(price)) for each category (GROUP BY category). We also assign the aliases (total_quantity and max_price, respectively). Finally, we order the output by total_quantity in ascending order (omitting the ASC keyword) and by max_price in descending order (using the DESC keyword). As expected, the table is sorted by the total number of items in each category, starting with costumes (which has the fewest items in stock). But notice that the accessories and masks categories both have 15 items in stock. In this case, sorting by the second column comes into play and these two rows are ordered according to the most expensive product in the corresponding category. Specifically, the accessories category comes first (as it includes Ghostbusters, which is priced at $13.95), while the masks category comes last (as the most expensive masks cost only $3.95). To learn more advanced ORDER BY usages – like putting expressions in the ORDER BY condition – check out this comprehensive guide. Time to Practice SQL ORDER BY! ORDER BY seems quite easy to grasp. However, sorting records in SQL has many nuances that are best learned with practice. I recommend starting with the SQL Basics course; among other things, it covers everything you need to know about sorting rows in SQL. The course is interactive and includes 149 coding challenges, from the very basic to the more advanced. If you are eager to build a solid SQL foundation, consider completing LearnSQL.com’s SQL Fundamentals track. It includes SQL Basics and two additional courses that cover standard SQL functions, creating and updating tables in SQL, and much more. Thanks for reading, and happy learning! Tags: sql learn sql order by You may also like What Does ORDER BY Do? We’ll go through multiple examples to see how SQL ORDER BY sorts data according to one or more columns in ascending or descending order. Read more How to Order Alphabetically in SQL Read more How to Order By Two Columns in SQL? Read more How to Sort Records with the ORDER BY Clause We'll take a closer look at the ORDER BY clause in SQL – how to write it, how it works, and what it does to a relational database. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.