SQL Order By (ASC and DESC)

When we query a database, the results we get back may not always be in the order we want them to be. That’s where the ORDER BY clause comes in handy. The ORDER BY clause is used to sort the result set in either ascending or descending order. In this article, we’ll cover the syntax for the ORDER BY clause and give some examples of how it can be used.

If you’re new to SQL, you may want to first check out our article on SQL Select to learn more about retrieving data from a database table. Additionally, the SQL WHERE with AND, OR, NOT Operators article can provide useful information on filtering data based on specific conditions.

Syntax

The syntax for the ORDER BY clause is as follows:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

The ORDER BY clause is used after the FROM clause and before any GROUP BY or HAVING clauses. You specify the column or columns you want to sort by, and you can specify whether you want the sort order to be ascending (ASC) or descending (DESC).

If you don’t specify a sort order for a column, the default sort order is ascending. You can sort by multiple columns by specifying them in the ORDER BY clause separated by commas.

Examples

Let’s take a look at some examples of how the ORDER BY clause can be used.

Suppose we have a table called employees with the following data:

If we want to retrieve all the employees from the table and sort them by salary in descending order, we can use the following SQL query:

SELECT * FROM employees ORDER BY salary DESC;

This would return the following result set:

Notice that the result set is sorted by salary in descending order, from highest to lowest.

We can also sort by multiple columns. For example, if we want to retrieve all the employees from the table and sort them first by age in ascending order, and then by salary in descending order, we can use the following SQL query:

SELECT * FROM employees ORDER BY age ASC, salary DESC;

This would return the following result set:

Notice that the result set is first sorted by age in ascending order, and then by salary in descending order.

Practice tasks

Beginner

1. Write a SQL query that retrieves all rows from the personal finance table and sorts them by date in ascending order.
2. Write a SQL query that retrieves all rows from the personal finance table and sorts them by amount in descending order.

Intermediate

1. Write a SQL query that retrieves all rows from the personal finance table and sorts them by category in ascending order, and then by date in descending order.
2. Write a SQL query that retrieves all rows from the personal finance table and sorts them by amount in ascending order, and then by date in ascending order.

Advanced

1. Write a SQL query that retrieves the total amount spent on food in April, grouped by day.
2. Write a SQL query that retrieves the total amount spent on each category in May, sorted by the total amount in descending order.

Conclusion

The ORDER BY clause is a powerful SQL feature that enables you to sort your query results in a specific order. In this article, we covered the syntax for the ORDER BY clause and provided examples of how it can be used to sort data in ascending or descending order based on one or more columns.

By mastering the ORDER BY clause, you can become a more efficient and effective data analyst, as sorting your query results can help you make sense of your data and make informed decisions. To further enhance your SQL skills, we recommend checking out our articles on SQL Insert Into, SQL Update, and SQL Delete Statements.