This post will discuss the simple but powerful clause ORDER BY. Prior to reading this article, you should have a basic knowledge of SQL concepts such as select statements. Below, we will walk through various use cases and examples of how to use the ORDER BY keyword.
What is ORDER BY in SQL?
The ORDER BY clause is used to sort a column of information into either ascending or descending order- to specify which use the keywords ASC or DESC respectively. The clause requires at least one column as input; each input can be ordered in ascending or descending order, with the default being ascending.
Let’s take a look at a few examples.
First will be the default case. If the order (ascending vs. descending) is not specified, ORDER BY defaults to an ascending list.
To list the contacts in descending order, add the keyword DESC following the ORDER BY clause:
ORDER BY can also be used across multiple columns. To list contacts in ascending order by name and by country include both columns separated by a “,”. When using multiple columns, the first column listed holds precedence for ordering, all subsequent columns will be used to order within the first specifications.
Using multiple columns can be particularly useful to order data; to go further with this clause, you can specify various orders for each column.
ORDER BY and collation:
Prior to ordering your data look into your workbench’s collation settings.
This piece of information will identify precedence in the ordering sequence and notify the query of how to handle special cases such as “A” vs “a”; choosing a collation ending in “ci” will create a database that is case insensitive where A = a.
Collation settings can also be used to change the language in which the database is written in- German versus English- to aid in determining precedence for order.
How can I use ORDER BY with a column I do not want to display?
Let’s say you want to use the table from above which identifies where customers live. You wish to order the customers by their country but only want to display their name and city.
ORDER BY can be used to specify an ordering column without having to display that column. An example of this is seen here.
|Patricio Simpson||Buenos Aires|
|Yvonne Moncada||Buenos Aires|
|Sergio Gutierrez||Buenos Aires|
So there you have it, the basics of ORDER BY. You can create lists in ascending or descending order, with one or multiples levels of ordering, or even order by a column that is not displayed.
Interested in more? To learn about why combining ORDER BY with DISTINCT won’t work to narrow down queries, check out this article by Luke Seder of JOOQ: