The ORDER BY clause is commonly used in SQL to sort the results of a query.
It allows you to specify the order in which the results are returned.
You can specify whether the results are sorted in ascending order or descending order. You can also specify multiple columns for sorting.
Example
Here’s an example to demonstrate the ORDER BY clause.
SELECT
VendorId,
ProductName,
ProductPrice
FROM Products
ORDER BY VendorId ASC;Result:
+------------+---------------------------------+----------------+ | VendorId | ProductName | ProductPrice | |------------+---------------------------------+----------------| | 1001 | Left handed screwdriver | 25.99 | | 1001 | Long Weight (blue) | 14.75 | | 1001 | Long Weight (green) | 11.99 | | 1002 | Sledge Hammer | 33.49 | | 1003 | Chainsaw | 245.00 | | 1003 | Straw Dog Box | 55.99 | | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | +------------+---------------------------------+----------------+
The ASC part means ascending. When you use the ORDER BY clause, it defaults to ascending, so you can omit the ASC part if you wish.
So we could also write the query like this:
SELECT
VendorId,
ProductName,
ProductPrice
FROM Products
ORDER BY VendorId;Descending Order
To sort it in descending order, use DESC.
SELECT
VendorId,
ProductName,
ProductPrice
FROM Products
ORDER BY VendorId DESC;Result:
+------------+---------------------------------+----------------+ | VendorId | ProductName | ProductPrice | |------------+---------------------------------+----------------| | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | | 1003 | Chainsaw | 245.00 | | 1003 | Straw Dog Box | 55.99 | | 1002 | Sledge Hammer | 33.49 | | 1001 | Left handed screwdriver | 25.99 | | 1001 | Long Weight (blue) | 14.75 | | 1001 | Long Weight (green) | 11.99 | +------------+---------------------------------+----------------+
Order By Multiple Columns
You can specify multiple columns with which to sort by. This enables you to specify how the rows should be sorted when there are multiple rows with the same value in the first sorted column.
It’s probably easier to demonstrate this with an example.
SELECT
VendorId,
ProductName,
ProductPrice
FROM Products
ORDER BY VendorId DESC, ProductName DESC, ProductPrice DESC;Result:
+------------+---------------------------------+----------------+ | VendorId | ProductName | ProductPrice | |------------+---------------------------------+----------------| | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | | 1003 | Straw Dog Box | 55.99 | | 1003 | Chainsaw | 245.00 | | 1002 | Sledge Hammer | 33.49 | | 1001 | Long Weight (green) | 11.99 | | 1001 | Long Weight (blue) | 14.75 | | 1001 | Left handed screwdriver | 25.99 | +------------+---------------------------------+----------------+
If we focus on the three rows with a VendorId of 1001, we can see that the previous example returned the product names in ascending order, but in this example we returned them in descending order. This is because, we nominated the ProductName as the second column in our ORDER BY clause, and we specified DESC for descending order.
The second column only takes effect if there are duplicates in the first ORDER BY column. If not, any subsequent columns are irrelevant as far as further sorting goes.
If we look at the ProductPrice column, we can see that this column had no effect on the sorting, even though we specified ProductPrice DESC. We can see that the prices are all in ascending order, despite our insistence that they should be in descending order. The reason this column had no effect is because there were no duplicates in the previously sorted column. Therefore, the ordering of the first two columns impacted the results, but the ordering of the third column did not.
This is not to say that the third column will never have any effect. If later on we inserted another Left handed screwdriver, but at a different price, then the ProductPrice column’s ordering would take effect, due to the fact that there are duplicate values in the ProductName column.
Mixing Ascending with Descending
You can mix each column with ascending and descending order. They don’t all have to be the same. For example, we could do this:
SELECT
VendorId,
ProductName,
ProductPrice
FROM Products
ORDER BY VendorId DESC, ProductName ASC, ProductPrice DESC;Result:
+------------+---------------------------------+----------------+ | VendorId | ProductName | ProductPrice | |------------+---------------------------------+----------------| | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | | 1003 | Chainsaw | 245.00 | | 1003 | Straw Dog Box | 55.99 | | 1002 | Sledge Hammer | 33.49 | | 1001 | Left handed screwdriver | 25.99 | | 1001 | Long Weight (blue) | 14.75 | | 1001 | Long Weight (green) | 11.99 | +------------+---------------------------------+----------------+
Order by Column Aliases
The ORDER BY clause accepts column aliases as a column to be ordered by.
For example, we could do this:
SELECT
VendorId AS ID,
ProductName AS Name,
ProductPrice AS Price
FROM Products
ORDER BY ID DESC, Name DESC, Price DESC;Result:
+------+---------------------------------+---------+ | ID | Name | Price | |------+---------------------------------+---------| | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | | 1003 | Straw Dog Box | 55.99 | | 1003 | Chainsaw | 245.00 | | 1002 | Sledge Hammer | 33.49 | | 1001 | Long Weight (green) | 11.99 | | 1001 | Long Weight (blue) | 14.75 | | 1001 | Left handed screwdriver | 25.99 | +------+---------------------------------+---------+
Order by Columns that are Not in the SELECT List
The ORDER BY clause accepts columns that aren’t specified in the SELECT list.
In other words, you aren’t required to select a column in order to sort by that column.
Example:
SELECT
ProductName AS Name,
ProductPrice AS Price
FROM Products
ORDER BY VendorId DESC, Name DESC, Price DESC;Result:
+---------------------------------+---------+ | Name | Price | |---------------------------------+---------| | Bottomless Coffee Mugs (4 Pack) | 9.99 | | Straw Dog Box | 55.99 | | Chainsaw | 245.00 | | Sledge Hammer | 33.49 | | Long Weight (green) | 11.99 | | Long Weight (blue) | 14.75 | | Left handed screwdriver | 25.99 | +---------------------------------+---------+
Here, we ordered by the VendorId column, even though we didn’t include it in the SELECT list.
Order by Column ID
The ORDER BY clause also accepts the column ID in place of the column name.
For example, we could do this:
SELECT
VendorId AS ID,
ProductName AS Name,
ProductPrice AS Price
FROM Products
ORDER BY 1 DESC, 2 DESC, 3 DESC;Result:
+------+---------------------------------+---------+ | ID | Name | Price | |------+---------------------------------+---------| | 1004 | Bottomless Coffee Mugs (4 Pack) | 9.99 | | 1003 | Straw Dog Box | 55.99 | | 1003 | Chainsaw | 245.00 | | 1002 | Sledge Hammer | 33.49 | | 1001 | Long Weight (green) | 11.99 | | 1001 | Long Weight (blue) | 14.75 | | 1001 | Left handed screwdriver | 25.99 | +------+---------------------------------+---------+
However, this is not recommended.
First, it makes the query harder for others to read and understand.
Second, if someone later changed the order of the columns in the SELECT list, they would also have to change the order of the ORDER BY list. It would be very easy to forget to do this, and the query’s results will end up in the wrong order.
Here’s an example of what I mean.
SELECT
ProductName AS Name,
VendorId AS ID,
ProductPrice AS Price
FROM Products
ORDER BY 1 DESC, 2 DESC, 3 DESC;Result:
+---------------------------------+------+---------+ | Name | ID | Price | |---------------------------------+------+---------| | Straw Dog Box | 1003 | 55.99 | | Sledge Hammer | 1002 | 33.49 | | Long Weight (green) | 1001 | 11.99 | | Long Weight (blue) | 1001 | 14.75 | | Left handed screwdriver | 1001 | 25.99 | | Chainsaw | 1003 | 245.00 | | Bottomless Coffee Mugs (4 Pack) | 1004 | 9.99 | +---------------------------------+------+---------+
All I did was move the ProductPrice column’s position in the SELECT list, and it completely messed up the ordering of the results.
Default Ordering
In SQL, if you don’t use the ORDER BY clause, there’s no guarantee what order your results will be in. Although it may look like your database is sorting the results by a particular column, this may not actually be the case.
In general, without an ORDER BY clause, data will be sorted in the order the in which it was loaded into the table. However, if rows have been deleted or updated, the order will be affected by how the DBMS reuses reclaimed storage space.
Therefore, don’t rely on the DBMS to sort the results in any meaningful order. If you want your results to be sorted, use the ORDER BY clause.
Ordering with Exceptions
You may encounter times where you need to include an exception in your ORDER BY clause.
For example, you want to order alphabetically by a column except for one row. You might want one row (or several rows) to appear at the top, while all the remaining rows are ordered in a particular order.
Fortunately, there’s an easy way to do this. See How to Write an ORDER BY Clause with Exceptions if you need to do this.