In relational databases, the SQL ORDER BY statement is a powerful tool for sorting data in a table. The statement is used to arrange queried data into rows in a specific order, based upon one or more columns. In this database programming tutorial, we will learn how to use the SQL ORDER BY statement to sort retrieved data in ascending or descending order.
Read: Best Courses for Database Administrators
SQL ORDER BY Statement Syntax
The syntax for the SQL ORDER BY statement is shown in the following example code:
SELECT columnA, columnB, columnC FROM table_example ORDER BY columnA, columnB, columnC ASC|DESC;
In the example code above, the SELECT statement is used to first select the columns to be displayed from a given table. Then, the FROM statement defines the table name the data will be pulled from. The ORDER BY statement then sorts the retrieved data in either ascending or descending order, based on one (or more) columns.
They keywords ASC and DESC are optional parameters of ORDER BY, which dictate the order of the sort. ASC – which is short for ascending – is the default sort order, while DESC – which is short for descending – sorts the data in the reverse order.
How to Sort Data in Ascending Order in SQL
In SQL databases, database developers can sort data in ascending order using the SQL ORDER BY statement and the optional parameter (discussed above) ASC, alongside the column (or columns) you want to sort by.
For instance, if a programmer or database administrator has a table named employees that has the columns: employee_id, first_name last_name, and hire_date. If we wanted to sort the queried data by ascending order by the last_name column, we could use the following example SQL query:
SELECT * FROM employees ORDER BY last_name ASC;
The above query chooses every column from the employees table. It then sorts the data in ascending order by the last_name column.
How to Sort Data in Descending Order in SQL
Sorting data in descending order in SQL is possible as well, also using the SQL ORDER BY statement. All database developers need to do is add the DESC keyword after the column (or columns) you intend to sort by. As an example, if you want to sort the employees table by descending order using the last_name column, you would use the following SQL query:
SELECT * FROM employees ORDER BY last_name DESC;
The above query retrieves all columns from the employees table and then sorts the data by last_name in descending order.
How to Sort by Multiple Columns in SQL Using ORDER BY
Database administrators and database programmers can also sort by multiple columns using the SQL ORDER BY statement. To do this, all you need to do is note which columns you want to sort by in the ORDER BY clause, using commas as separators. The retrieved data gets sorted first by the first column, and then using the second column. If there are more than two sort clauses, the sort continues in order as expected. For instance, if we wanted to sort our employees table by the last_name column, and then using the first_name column, we would use the SQL query below:
SELECT * FROM employees ORDER BY last_name ASC, first_name ASC;
The above SQL query retrieves every column from the employees table and then sorts the information by ascending order using the last_name column. In cases where more than one employee has the same last name, then the returned data will then be sorted in ascending order by the first_name column.
Read: Project Management Software for Database Developers
How to Sort Data by Calculated Columns in SQL
Database developers and db admins can also sort data by columns in a table using calculated columns. Calculated columns are columns that are created when we use a calculation or equation on one or more existing columns in a table. For example, in our table employees if we wanted to sort by the sum of the salary and bonus columns, in ascending order, we could use the following SQL query:
SELECT *, salary + bonus as total_income FROM employees ORDER BY total_income ASC;
The above SQL query chooses all columns from the employees table and then creates a calculated column named total_income by using the sum of salary and bonus. The returned data is then sorted in ascending order courtesy of the total_income column.
How to Sort Data by Alias Names in SQL with ORDER BY
There are some instances where database programmers might need to sort data using an alias name that we define in our SELECT statement. To achieve this, put the alias name in the ORDER BY clause.
As an example, using our employee table let’s say we wanted to sort our data using a total_income table we set as an alias name, by descending order, we can use the following SQL query and statement:
SELECT *, salary + bonus as total_income FROM employees ORDER BY total_income DESC;
Our example query works by selecting every column from our employees table and creates a new calculated column named total_income (our alias named column) by adding together the salary and bonus columns. This data is then sorted in descending order using our new total_income column.
Sorting Data by NULL Values
Sometimes you need to sort data in a relational database that contains a NULL value; querying this sort of data can cause problems if the database developer does not account for this possibility. The reason for this is that NULL is not technically a value, but, instead, a placeholder representing the absence of a value. If you are sorting data containing NULL, they are typically sorted at either the beginning or end of the results, depending on how you set the sort order.
Developers can specify where NULL values are sorted using the NULLS FIRST or NULLS LAST keywords in the ORDER BY clause. Let’s say you wanted to sort the employees” table in ascending order using the manager_id column, with NULL values sorted last. To do so, we could use the following SQL query:
SELECT * FROM employees ORDER BY manager_id ASC NULLS LAST;
The query above selects all columns from the employees table, then sorts the data in ascending order by the manager_id column, sorting the NULL values last.
You can learn more about NULL values in our tutorial: Working with NULL Values in SQL.
Final Thoughts on SQL ORDER BY
The SQL ORDER BY statement is used to sort data in a relational database. Using it, database developers and database administrators can sort data in ascending or descending order, using one or more columns, calculated columns, or alias names. You can also specify where NULL values should be sorted in the results, avoiding some common query errors caused by NULL values. Understanding how to use the ORDER BY statement in SQL lets programmers and admins manipulate data to create more robust queries and make your relational database more efficient.
