Data sorting comes into play into scenarios where you need to organize the data into specific order which is useful for data visualization, gathering data insights, and more. It also makes the process of retrieving, cleaning, and analyzing the data much easier.
In SQL, we have the ORDER BY clause which provides us with the functionality to sort the data into ascending or descending order.
In this tutorial, we will learn how to sort the data in ascending order using the ORDER BY and the ASC keyword.
NOTE: For demonstration purposes, we will use the Sakila sample database and MySQL version 8.0. Feel free to reference and use any dataset that you deem applicable.
SQL Ascending Order
The ascending order in SQL simply refers to a method of sorting the data in a query result. The ascending order may be either numerical or alphabetical depending on the target sort column.
When we apply the ascending order to a column sort, SQL will organize the data that range from the smallest (lowest) value to the largest (highest) value.
In the case of strings, the ascending order uses alphabetical order where A is the lowest and Z is the highest.
SQL ORDER BY
As you can guess, the way we perform the sorting, ascending, or descending in SQL is by the use of the ORDER BY clause.
The ORDER BY clause allows us to sort the result set of a query based on one or more columns. We can express the syntax of the clause as follows:
FROM table
ORDER BY column_to_sort;
After the ORDER BY clause, we specify the sorting criteria. This is basically the column that we wish to order.
SQL ASC Keyword
The ASC keyword in the context of ORDER BY clause tells the database engine to sort the data in ascending order.
It is good to keep in mind that this is the default option for the ORDER BY clause. Hence, even if we do not explicitly tell SQL to sort the data in ascending order, it will automatically do it as the default operation.
Here is the syntax on how we apply the ASC keyword in the ORDER BY clause:
FROM table_name
ORDER BY column ASC;
This should sort the specified column into ascending order.
Example 1: Basic Usage
Let us look at an example usage of the ORDER BY clause. Consider the “film” table from the Sakila sample database. Suppose we wish to sort the data from the highest rental price in ascending order.
title,
release_year ,
length,
rental_rate
FROM
film
ORDER BY
rental_rate ASC;
In this case, we use the “rental_rate” in the ORDER BY clause to quickly sort the films from the lowest to the highest rental rate.
The resulting output is as follows:
Example 2: Sorting Multiple Columns
SQL also allows us to provide more than one column as the sorting parameter. This can be very useful when we need to sort the data based on more than one criterion.
To accomplish this, we can simply list multiple columns in the ORDER BY clause separated by a comma.
Let us take the “payment” table from the Sakila table. We can sort based on the amount and the “payment_date” in ascending order as shown in the following example query:
customer_id,
amount,
payment_date
FROM
payment
ORDER BY
amount ASC,
payment_date ASC;
This query should fetch the “customer_id”, “amount”, and “payment_date” columns from the “payment” table. However, the query first sorts the result in ascending order based on the payment amount followed by the payment date.
This provides double sorting criteria as shown in the resulting table:
Conclusion
In this tutorial, we dived deep into the process of sorting the data in SQL using the ORDER BY clause. We also learned how we can use the ASC keyword to sort the data in ascending order. Finally, we explored how we can sort the data using multiple columns.
