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.

SELECT ContactName, Country FROM Customers ORDER BY ContactName;
ContactName Country
Alejandra Camino Spain
Alexander Feuer Germany
Ana Trujillo Mexico
Anabela Domingues Brazil
Andre Fonseca Brazil
Ann Devon UK

To list the contacts in descending order, add the keyword DESC following the ORDER BY clause:

SELECT ContactName, Country FROM Customers ORDER BY ContactName DESC;
ContactName Country
Zbyszek Poland
Yvonne Moncada Argentina
Yoshi Tannamuri Canada
Yoshi Latimer USA
Yang Wang Switzerland

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.

SELECT ContactName, Country FROM Customers ORDER BY Country, ContactName;
ContactName Country
Patricio Simpson Argentina
Sergio Gutierrez Argentina
Yvonne Moncada Argentina
Georg Pipps Austria
Roland Mendel Austria
Catherine Dewey Belgium

Using multiple columns can be particularly useful to order data; to go further with this clause, you can specify various orders for each column.

SELECT ContactName, Country FROM Customers ORDER BY Country DESC, ContactName ASC;
ContactName Country
Carlos Gonzalez Venezuela
Carlos Hernandez Venezuela
Felipe Izquierdo Venezuela
Manuel Pereira Venezuela
Art Braunschweiger USA
Fran Wilson USA

ORDER BY and collation:

Prior to ordering your data look into your workbench’s collation settings.

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.

SELECT CustomerName, City FROM Country ORDER BY Country;
ContactName City
Patricio Simpson Buenos Aires
Yvonne Moncada Buenos Aires
Sergio Gutierrez Buenos Aires
Roland Mendel Graz
Georg Pipps Salzburg
Catherine Dewey Bruxelles

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:

How SQL Distinct and ORDER BY are related