How to Sort in SQL

Problem:

You would like to sort the result of an SQL query in ascending or descending order.

Example:

Our database has a table named salary_information with data in the columns id, first_name, last_name, and monthly_earnings. We want to sort the employees by their monthly_earnings in descending order.

Solution:

We will use an ORDER BY clause. Here is the query:

SELECT
  first_name,
  last_name,
  monthly_earnings
FROM salary_information
ORDER BY monthly_earnings DESC;

Here is the result of the query:

Now, we can see that Alex Watkins is first on the list, which means he earns the most money.

Discussion:

Use an ORDER BY clause if you want to sort the dataset in either ascending or descending order. The syntax for an ORDER BY clause is as follows:

SELECT col1, col2, …
FROM table
ORDER BY col1, col2, … ASC|DESC;

In the above, ASC|DESC means that you should choose either the keyword ASC (ascending) or DESC (descending) to order the dataset the way you want.

Besides sorting by numeric columns, you can also sort by text columns. An ORDER BY clause will sort text columns in alphabetical order.

Instead of the column name, you can also use the position of the column counting from the left. So, in our example, instead of writing:

ORDER BY monthly_earnings DESC

you can also write:

ORDER BY 3 DESC

If you want to sort the table from the example by the first_name column in alphabetical (ascending) order, you can use the following query:

SELECT
  first_name,
  last_name,
  monthly_earnings
FROM salary_information
ORDER BY first_name ASC;

It is also worth noting that you can sort by multiple columns. This is helpful when some values in a given column are repeated and you need additional sorting. In this case, separate the column names with commas in the ORDER BY clause.

You can even sort in ascending order by one column and in descending order by another. The following illustrates the syntax of this combination:

ORDER BY Col1 ASC, Col2 DESC;

If you omit the ASC or DESC keyword, an ascending sort is performed by default.