How to Sort Records with the ORDER BY Clause

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:

Here is how it looks after an ascending sort by the furniture column:

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:

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:

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:

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:

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:

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:

Compare those results with the ones below, which use only the CASE expression:

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 LearnSQL.com's SQL Basics course.