ORDER BY CLAUSE IN SQL

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 tableORDER 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.

ORDER BY One Column

Here is the table with our products:

products
id	name	category	quantity	price
11	Wonder Woman	costumes	2	19.95
12	Vampire	masks	4	3.95
13	Magic Wand	accessories	6	2.50
14	Wizard of Oz	hats	3	3.95
15	Superman	costumes	3	14.95
16	Hulk	costumes	2	12.50
17	Witch	hats	4	4.95
18	Rapunzel	costumes	0	NULL
19	Snow White	costumes	1	23.95
20	Freddy Krueger	masks	3	3.95
21	Alien	masks	0	NULL
22	Cowboy	hats	3	4.95
23	Ghostbuster	accessories	2	13.95
24	Sword	accessories	7	3.50
25	Zombie	masks	8	2.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 productsORDER BY name;

And here’s the result, with all the records sorted alphabetically based on the product name:

id	name	category	quantity	price
21	Alien	masks	0	NULL
22	Cowboy	hats	3	4.95
20	Freddy Krueger	masks	3	3.95
23	Ghostbuster	accessories	2	13.95
16	Hulk	costumes	2	12.50
13	Magic Wand	accessories	6	2.50
18	Rapunzel	costumes	0	NULL
19	Snow White	costumes	1	23.95
15	Superman	costumes	3	14.95
24	Sword	accessories	7	3.50
12	Vampire	masks	4	3.95
17	Witch	hats	4	4.95
14	Wizard of Oz	hats	3	3.95
11	Wonder Woman	costumes	2	19.95
25	Zombie	masks	8	2.95

Now let’s sort 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 productsORDER BY price DESC;

id	name	category	quantity	price
19	Snow White	costumes	1	23.95
11	Wonder Woman	costumes	2	19.95
15	Superman	costumes	3	14.95
23	Ghostbuster	accessories	2	13.95
16	Hulk	costumes	2	12.50
17	Witch	hats	4	4.95
22	Cowboy	hats	3	4.95
14	Wizard of Oz	hats	3	3.95
12	Vampire	masks	4	3.95
20	Freddy Krueger	masks	3	3.95
24	Sword	accessories	7	3.50
25	Zombie	masks	8	2.95
13	Magic Wand	accessories	6	2.50
21	Alien	masks	0	NULL
18	Rapunzel	costumes	0	NULL

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_priceFROM productsGROUP BY categoryORDER BY avg_price DESC;

Note that the ORDER BY clause is placed last, after the GROUP BY clause. Here’s the result:

category	avg_price
costumes	17.84
accessories	6.65
hats	4.62
masks	3.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_priceFROM productsGROUP BY categoryORDER 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’.

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, priceFROM productsORDER BY price DESC, name;

id	name	price
19	Snow White	23.95
11	Wonder Woman	19.95
15	Superman	14.95
23	Ghostbuster	13.95
16	Hulk	12.50
22	Cowboy	4.95
17	Witch	4.95
20	Freddy Krueger	3.95
12	Vampire	3.95
14	Wizard of Oz	3.95
24	Sword	3.50
25	Zombie	2.95
13	Magic Wand	2.50
21	Alien	NULL
18	Rapunzel	NULL

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, priceFROM itemsORDER 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:

SELECTcategory, SUM(quantity) AS total_quantity, MAX(price) AS max_priceFROM itemsGROUP BY categoryORDER BY total_quantity, max_price DESC;

category	total_quantity	max_price
costumes	8	23.95
hats	10	4.95
accessories	15	13.95
masks	15	3.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).