Online College Courses for Credit

+
1 Tutorials that teach ORDER BY to Sort Data
Take your pick:
ORDER BY to Sort Data

ORDER BY to Sort Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that uses ORDER BY to sort data based on columns.

(more)
See More
Tutorial

what's covered
This tutorial explores the ORDER BY clause within a SELECT statement to sort data based on columns in three parts:
  1. Sorting By One Column
  2. Ascending or Descending
  3. Cascading Order Sequence

1. Sorting By One Column

The ORDER BY clause is quite useful when we want to list the records in a specific order based on the column list. When we simply query from a table, by default, the result set is sorted 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;

File:11337-1120-1.png

If we wanted to sort the result based on the billing_country, we can add a clause to the SELECT statement after the FROM clause:

SELECT *
FROM invoice
ORDER BY billing_country;

File:11338-1120-2.png

You should notice that the data is now sorted based on the billing_country as 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. One thing to notice is that the invoice_id is no longer ordered as the results have been reordered based on the billing_country.

Let us 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;

File:11339-1120-3.png

If we wanted to order it by the first_name, we can simply add it to 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:


File:11340-1120-4.png


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;


File:11341-1120-5.png

You will notice that this command returns 412 rows with a lot of the initial ones starting with 1. This may be useful but what if we wanted to find the highest total first? We can add the keyword DESC to the ORDER BY clause beside the column to sort it accordingly.

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

This would have the resulting data:


File:11342-1120-6.png

You can 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 of the same query. For example, we may want to list the customers in a phone directory. In those cases, we would want to sort it 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 be easily 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;


File:11343-1120-7.png

For our current data, it may not appear useful yet as we only have 59 customers in the table. However, with more customers, you will have many more identical first and last names.

Source: Vincent Tran

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 choices for which columns you want the query to provide.

summary
We can sort the result set in ascending order using the ORDER BY clause, or use DESC to sort it in descending order. We can also sort using the cascading order sequence by listing multiple columns separated by commas.