Back to cookbooks list Articles Cookbook

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.

idfirst_namelast_namemonthly_earnings
1CalvinRios3500
2AlanPaterson4000
3KurtEvans2300
4AlexWatkins5500

Solution:

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

We’ll use the function CURRENT_DATE to get the current date:

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

Here is the result of the query:

first_namelast_namemonthly_earnings
AlexWatkins5500
AlanPaterson4000
CalvinRios3500
KurtEvans2300

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.

Recommended courses:

Recommended articles:

See also: