Back to articles list May 18, 2017 - 7 minutes read How to Sort Records with the ORDER BY Clause Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: ORDER BY sorting records Relational databases don't store records in alphabetical, numerical, ascending, or in any particular order. The only way to order records in the result set is to use the ORDER BY clause. You can use this clause to order rows by a column, a list of columns, or an expression. You can also order rows using the CASE expression. In this post, we'll take a look at the ORDER BY clause – how to write it, how it works, and what it does. ORDER BY Syntax The ORDER BY clause is used to sort rows in a result table. Here is its syntax: SELECT list_column FROM table ORDER BY column ; Using ORDER BY with One Column The example below shows records in the furniture_data table that have been sorted in ascending order according to the furniture column. Ascending is the default sort order in an ORDER BY clause. SELECT id, furniture, points FROM furniture_data ORDER BY furniture ; As you can see, the ORDER BY clause is placed after the FROM statement. After the ORDER BY keyword, we have the column or columns that the rows will be sorted by. In other words, you are telling the database "Take the id, furniture, and points columns from the furniture_data table and put them in order based on the furniture column". You can specify how results are sorted, either in ascending (A-Z) or descending (Z-A) order. For ascending order, you can use the ASC keyword, but it is not necessary. For descending order, you must use the DESC keyword. So, after the column name, you can put the ASC or DESC keyword. The example below sorts data in ascending order by the "furniture" column: SELECT id, furniture, points FROM furniture _data ORDER BY furniture ASC ; Remember, you don't need the ASC keyword to specify an ascending sort; SQL does it by default. How does SQL's GROUP BY clause work when #NULL values are involved? Find out! https://t.co/yaTfYvwVix pic.twitter.com/ELx0AiLnnN — Vertabelo (@Vertabelo) April 29, 2017 Next, look at the data in the furniture_data table, which we will use in this article's remaining examples: id furniture points price 1 chair 5 120 2 sofa 8 950 3 table 2 1350 4 bookcase 5 200 5 bed 3 2000 Here is how it looks after an ascending sort by the furniture column: id furniture points 5 bed 3 4 bookcase 5 1 chair 5 2 sofa 8 3 table 2 What about doing a descending sort on the same column? SELECT id, furniture, points FROM furniture _data ORDER BY furniture DESC ; Here is the result for the descending sort: id furniture points 3 table 2 2 sofa 8 1 chair 5 4 bookcase 5 5 bed 3 Using ORDER BY with Multiple Columns The last examples sorted data using just one column. However, if some records in this column have identical values, it could affect how the results are shown. In this case, we need to order rows using an additional column. The next example shows how to use more than one column to order data: SELECT id, furniture, points FROM furniture _data ORDER BY point DESC , furniture; This example sorts rows in descending order according to the points column. If two or more records have the same value, the database uses the furniture column to sort these records in ascending order. Here are the results: id furniture points 2 sofa 8 4 bookcase 5 1 chair 5 5 bed 3 3 table 2 The bookcase and chair rows have the same number of points (5), so these rows are also sorted by the values in the furniture column. The sort is alphabetical, so the bookcase row is shown before the chair row. Expressions in the ORDER BY Clause Sometimes we need to sort records by the result of a calculation rather than by column values. Imagine that you would like to sort data retrieved from the furniture_data table according to the amount of a price discount. You would calculate the discount by multiplying the value in the points column by 120, and then dividing this by 100. Here is how this would look in SQL code: SELECT id, furniture, points, price FROM furniture _data ORDER BY price-120*points/100; Here is the result: id furniture points price 1 chair 5 120 4 bookcase 5 200 2 sofa 8 950 3 table 2 1350 5 bed 3 2000 Using ORDER BY with CASE Ordering By the CASE Expression Besides sorting rows using one or more columns, you can also use ORDER BY with simple and searched CASE expressions. (To learn more about CASE, read this article.) Why use CASE in an ORDER BY clause? You may want to get even more specific on how the rows are sorted, or you may want to retrieve only some of the rows (i.e. those that meet certain conditions). When you need to add IF-THEN logic to a SELECT statement, CASE is here to help –> https://t.co/EMwAZDO2T5 #SQL #SQLServer #SQLTip pic.twitter.com/dPHNp6IsjS — Vertabelo (@Vertabelo) May 8, 2017 The example below shows how to use CASE to sort data in a specific way. Imagine that you want to retrieve data from the furniture_data table, but that you want the rows with an even number of points displayed at the top of the results. You can't do this using columns as sorting criteria; you need CASE. Below is an example of the relevant code: SELECT id, furniture, points FROM furniture _data ORDER BY CASE WHEN points%2=0 THEN 1 ELSE 2 END ; Here is what the results look like: id furniture points 2 sofa 8 3 table 2 1 chair 5 4 bookcase 5 5 bed 3 How did the CASE expression do this? It checked if the number in the points column could be divided by 2 without a remainder. If it could, a 1 was returned and the row was shown in the top part of the results. If not, a 2 was returned and the row was shown in the bottom of the results. If you want to have the furniture records with an odd number of points listed first, all you need to do is change the sort order to descending: SELECT id, furniture, points FROM furniture _data ORDER BY CASE WHEN points%2=0 THEN 1 ELSE 2 END DESC ; The result: id furniture points 1 chair 5 4 bookcase 5 5 bed 3 2 sofa 8 3 table 2 Ordering with CASE and a Column You can use other things along with a CASE statement, such as a column or even another CASE expression. Look at the following example, which uses a CASE expression and a column to order data. SELECT id, furniture, points FROM furniture _data ORDER BY CASE WHEN points%2=0 THEN 1 ELSE 2 END , furniture DESC ; First, the records are ordered according to the CASE expression, which checks if points values are even or odd. As before, even numbers are listed first. But we want to sort the groups even more. So we use the furniture column to order results within the CASE. Because we've used the DESC keyword, rows are listed in reverse alphabetical order. Here are the results: id furniture points 3 table 2 2 sofa 8 1 chair 5 4 bookcase 5 5 bed 3 Compare those results with the ones below, which use only the CASE expression: id furniture points 2 sofa 8 3 table 2 1 chair 5 4 bookcase 5 5 bed 3 Want to Learn More? This article just got you started with the ORDER BY clause. The best way to learn more is to try it out for yourself, using Vertabelo Academy's SQL Queries course. Tags: ORDER BY sorting records You may also like Useful SQL Patterns: Date Generator Do you have to manually add all the missing days? No. You can use the SQL pattern known as a date generator to fill in the gaps. Read more Using CASE with Data Modifying Statements The CASE expression is a useful part of #SQL and one that you'll employ frequently. What happens when you combine CASE with SQL's data modifying statements? Read more How to Start Thinking in SQL If you want to know how to write better SQL queries, change the way you think. Instead of using the language, start thinking in SQL. Read more Essential SQL Terms to Know for Beginners and Pros In this article, we’ll explore some basic SQL database terminology you need to know to succeed in the data analysis world. Read more SQL Order of Operations Is the order in which SQL operations are executed important? Improve your SQL skills by learning the order of operations execution in SQL. Read more SQL INSERT, SQL UPDATE, SQL DELETE – Oh My! Need to understand SQL INSERT, UPDATE, or DELETE? We'll get you up to speed with a comprehensive How-To and useful examples! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.