ORDER BY to Sort Data

1. Sorting By One Column

When we simply query from a table, the result set is sorted by default based on the order of the inserted data. In most relational databases, a unique identifier is automatically generated through an auto-incremented value. In Postgres, this is called a serial. Since this value is automatically generated when data is inserted into a table, you will generally see the data sorted in this way. For example, if we query the invoice table, we will see it ordered automatically by the invoice_id.

SELECT * 
FROM invoice;

The ORDER BY clause is useful for when we want to list records in a specific order. For example, If we wanted to sort the result based on the billing_country, we can add an ORDER BY clause to the SELECT statement after the FROM clause:

SELECT * 
FROM invoice 
ORDER BY billing_country;

Notice that the data is now sorted based on the billing_country. It starts with Argentina for the first row, continues with all of the other rows that have the value of Argentina, followed by Australia, and so forth. Also notice that the invoice_id is no longer in order, as the results have been reordered based on the billing_country.

Let's look at the customer table and see what the results would look like without ordering the data:

SELECT customer_id, first_name, last_name 
FROM customer;

If we wanted to order it by the first_name, we can simply add the ORDER BY clause:

SELECT customer_id, first_name, last_name 
FROM customer
ORDER BY first_name;

The result set in the customer table would now be sorted by the first_name column:

2. Ascending or Descending

By default, when we use the ORDER BY clause in a SELECT statement, it sorts the data in ascending order. However, there are instances where we may want to sort the data in descending order. For example, if we wanted to query the invoice table and display the result set based on the total, we could sort it like this:

SELECT invoice_id, customer_id, total 
FROM invoice
ORDER BY total;

Notice that this command returns 412 rows, with a lot of the initial rows starting with 1. What if we wanted to find the highest total first? We can add the keyword DESC to the ORDER BY clause after the column to sort it accordingly.

SELECT invoice_id, customer_id, total 
FROM invoice
ORDER BY total DESC;

This would result in the following:

You can also use ASC in place of DESC to display the result in ascending order, although this won’t be any different than if you just listed the column without ASC added.

These two commands would result in the same result set, in the same order:

SELECT * 
FROM invoice
ORDER BY total ASC;

And

SELECT * 
FROM invoice
ORDER BY total;

3. Cascading Order Sequence

We may want to sort the data by multiple parts in the same query. For example, think about listing customers in a phone directory. In that case, we would want to sort based on the last name, first name, and the company. We would do this in three stages. First, ORDER BY last_name. Then, within the matching last_name values, we would ORDER BY first_name. Next, within the matching last_name and first_name values, we would ORDER BY the company. This type of multilevel ordered sequence is called the cascading order sequence. It can easily be created by listing the columns, separated by commas, after the ORDER BY clause.

SELECT customer_id, last_name, first_name, company 
FROM customer
ORDER BY last_name, first_name, company;

This cascading order sequence may not appear particularly useful for our data set, since we only have 59 customers in the table. However, with more customers, you will have many more identical first and last names, and multilevel sorting becomes more important.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own ORDER BY clauses.