Sorting with ORDER BY

The last time you were purchasing a ticket or hotel online, probably saw the results of your search by some ordering typically by price or relevance. This is often always important as it allows you to eliminate choices and narrow into specifics of what you are really trying to purchase. The same concept is similar to SQL querying. Often times, you will need to order the results of your query in a dimension that provides insights on particular questions

By default, the output of your query is not ordered, at least in the way you need them to be. To solve this, SQL has an ORDER BY clause that provides sorting the output by ordering a column or any number of columns.

Let's demonstrate an example. Below, we write a query that returns the customer_id, country, and credit_limit from the customers table. The query looks like below:

select customer_id, country, credit_limit
from customers

| customer_id | country     | credit_limit |
| ----------- | ----------- | ------------ |
| 2001        | USA         | 5000         |
| 2002        | UK          | 7500         |
| 2003        | Canada      | 3000         |
| 2004        | Australia   | 8000         |
| 2005        | France      | 6000         |
| 2006        | Germany     | 4500         |
| 2007        | Japan       | 9000         |
| 2008        | Spain       | 5500         |
| 2009        | Italy       | 7000         |
| 2010        | Netherlands | 6500         |

ORDER BY

Suppose the question we have is, we would like to quickly look at the countries where customers have low invoice values. We can write a query below to quickly get the results.

select customer_id, country, credit_limit
from customers
order by credit_limit

| customer_id | country     | credit_limit |
| ----------- | ----------- | ------------ |
| 2003        | Canada      | 3000         |
| 2006        | Germany     | 4500         |
| 2001        | USA         | 5000         |
| 2008        | Spain       | 5500         |
| 2005        | France      | 6000         |
| 2010        | Netherlands | 6500         |
| 2009        | Italy       | 7000         |
| 2002        | UK          | 7500         |
| 2004        | Australia   | 8000         |
| 2007        | Japan       | 9000         |
| 2007        | Japan       | 9000         |

By default, using the order by clause on the credit_limit column returns all the data sorted by credit_limit from the lowest to the highest.

ASC and DESC

We can use the keywords ASC and DESC to sort the results by ascending and descending order respectively. As seen above, ORDER BY alone sorts the output by ascending order. Below is an example of how to use the keywords to sort by descending order.

select customer_id, country, credit_limit
from customers
order by credit_limit DESC

| customer_id | country     | credit_limit |
| ----------- | ----------- | ------------ |
| 2007        | Japan       | 9000         |
| 2004        | Australia   | 8000         |
| 2002        | UK          | 7500         |
| 2009        | Italy       | 7000         |
| 2010        | Netherlands | 6500         |
| 2005        | France      | 6000         |
| 2008        | Spain       | 5500         |
| 2001        | USA         | 5000         |
| 2006        | Germany     | 4500         |
| 2003        | Canada      | 3000         |

Notice that the output is now ordered by descending order of the credit_limit column. We can tell that Spain has a customer with the highest credit limit.

Sorting By Multiple Columns

We can also order the results by multiple columns rather than a specific column. An example use case is if we wanted to see each country and their total invoice order by descending order. It can quickly tell us the highest credit_limit by each country. We can implement this by using the following query.

select customer_id, country, credit_limit
from customers
order by country ASC, credit_limit DESC

| customer_id | country     | credit_limit |
| ----------- | ----------- | ------------ |
| 2004        | Australia   | 8000         |
| 2003        | Canada      | 3000         |
| 2005        | France      | 6000         |
| 2006        | Germany     | 4500         |
| 2009        | Italy       | 7000         |
| 2007        | Japan       | 9000         |
| 2010        | Netherlands | 6500         |
| 2008        | Spain       | 5500         |
| 2002        | UK          | 7500         |
| 2001        | USA         | 5000         |

From the results we can easily see that the highest customer credit_limit from Australia was 8000, Canada was is 3000 and France was at 6000

Sorting by Column Number

A commonly used technique to make querying easy is to use column numbers rather than specific column names in the ORDER BY clause (and other clauses as well). For example, let's replicate the same query above and replace the ORDER BY parameters by numbers 2 and 3.

select customer_id, country, credit_limit
from customers
order by 2 ASC, 3 DESC

| customer_id | country     | credit_limit |
| ----------- | ----------- | ------------ |
| 2004        | Australia   | 8000         |
| 2003        | Canada      | 3000         |
| 2005        | France      | 6000         |
| 2006        | Germany     | 4500         |
| 2009        | Italy       | 7000         |
| 2007        | Japan       | 9000         |
| 2010        | Netherlands | 6500         |
| 2008        | Spain       | 5500         |
| 2002        | UK          | 7500         |
| 2001        | USA         | 5000         |

The output is identical. It is ,in fact, the same. By specifying ORDER BY 2, 3 we are referencing to the second and third column we have selected in our query and using them as the sorting parameters

This concludes our discussion on sorting the output. Next, we introduce a concept around filtering results with the WHERE clause