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
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
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
So, after the column name, you can put the
DESC keyword. The example below sorts data in ascending order by the “
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.
— 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
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).
— 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
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 ;
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
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.