SQL ORDER BY Clause with 7 Examples (2024)

Get to know the SQL ORDER BY clause! Our practical and easy examples will help you understand its syntax, common uses, and best practices.

SQL or Structured Query Language lets you “talk” to a database. It enables you to create, retrieve and manipulate the data in a relational database. This language has become so ubiquitous that hardly any data-related field has been untouched by it.

If you’re interested in the SQL ORDER BY clause, I’m assuming you have either already started learning SQL or are planning to start it soon. This clause is used for sorting results in a given order. In this article, we’ll cover some practical examples to demonstrate its use. If you’re not already familiar with the fundamentals of SQL queries, I recommend taking our SQL Basics course.

Let’s dive straight into the SQL ORDER BY clause.

What Is the SQL ORDER BY Clause?

As we already said, the SQL ORDER BY clause is used to arrange query results in a particular order. Suppose you are a college professor and you want to print a list of the students enrolled in your class in alphabetical order. If you have this information stored in some database, writing a simple query will give you the results.

In my experience, ORDER BY is one of the most useful SQL constructs; almost all reports and analyses have some use of it.

Let me take you through an example that explains how to write a simple query using ORDER BY.

Example 1: ORDER BY Using a Numerical Column

Imagine that you work as a sales manager in a company. Your company database stores sales data for each salesperson in the following table, called sales_performance:

Now say you want to retrieve this list of salespeople. You wanted them listed by their ID number in ascending order (i.e. smallest to largest, A-Z, etc.). Your query will look something like this:

SELECT * FROM sales_performanceORDER BY sales_person_id;

Output:

The query works by returning all records and columns from the sales_performance table and then arranging the results based on the numerical order of the column sales_person_id.

It’s important to note that ORDER BY sorts in ascending order by default; while you can use the ASC keyword to indicate you want results in ascending order, you don’t have to include it. In a field with numbers, ascending order puts the smallest number first, followed by the next smallest, and so on. The biggest number will come last.

Example 2: ORDER BY Using a Text Column

You can also use the same clause to sort column values in alphabetical order. For example, let’s say you wanted a list of salespeople ordered by the sales reps’ first names. Here’s the query:

SELECT * FROM sales_performanceORDER BY name;

Output:

Records are now sorted in alphabetical order according to the name. In ascending order, this means that names are listed alphabetically from A to Z.

Example 3: ORDER BY Using a Date Column

You can also sort results by a date column like joining_date. In the case of the date column, the default query returns the records from the oldest to the newest date:

SELECT * FROM sales_performanceORDER BY joining_date;

Output:

Here the results are arranged by joining_date, from the oldest to the newest.

Example 4: Ordering in Descending Order

So far, all the records have been sorted in ascending order. However, you can arrange the records in descending order – just write the DESC keyword after the column name.

For instance, say you want to arrange this list by total sales value achieved by each salesperson. You want to see the largest number (i.e. the highest sales) first, so you’ll have to sort in descending order. Let’s try the following query:

SELECT * FROM sales_performanceORDER BY total_sales_value DESC;

Output:

The results are sorted in descending order (10-1) according to the total sales value. You can also do this with text or date values; in this case, the results will be ordered Z to A (text) or newest to oldest (date).

The keywords DESC or ASC can be used as needed. If you omit the keyword, the results will be in ascending order.

Example 5: ORDER BY Using a Column Number

You can also reference a column using a numerical reference based on the order of columns in the table. In other words, you’d use a number instead of the column name. Take a look at the following example for more clarity:

SELECT sales_person_id, name FROM sales_performanceORDER BY 2;

Output:

The column name is the second column in our table. We want to order the values returned by the query by the sales rep name, but instead of writing name after ORDER BY, we wrote 2.

Note: This approach is only recommended for interactive querying. It’s not recommended in writing scripts, as queries with numbers in ORDER BY are difficult to manage.

Example 6: Sorting Multiple Columns

While writing real queries, your requirements are rarely as simple as the examples we’ve used; you may have to sort your values based on multiple columns.

The ORDER BY clause allows you to do that by specifying a list of columns; just separate the column names with commas. You can use the keywords ASC or DESC (if desired) with each column to sort that column in ascending or descending order.

Let’s say you need to display table records in decreasing order of territory and then by salesperson name. Your query will look something like this.

SELECT * FROM sales_performanceORDER BY territory DESC, name ASC;

Output:

You will notice in the output that the column territory is arranged in reverse alphabetical (i.e. descending) order. For the territory of London, the records are shown in alphabetical order by name.

Example 7: Using ORDER BY with Expressions

You can also sort by more than just column names; SQL allows you to use expressions when sorting values. For instance, say you want to order by concatenating territory and name in ascending order. Here’s the query:

SELECT *FROM sales_performanceORDER BY CONCAT(territory, name);

Output:

Here the records are ordered by a concatenation of territory and name. So Bavaria and Gregorio become first in alphabetical order and Paris Pierre is the last.

You can use CASE in ORDER BY to produce really precise orderings.

Want to Practise Using ORDER BY in SQL?

I hope these examples have clearly demonstrated the use of the ORDER BY clause. Sorting data is a key need in most views and reports, which makes it important to master the use of ORDER BY. And the key to becoming good at writing queries is practice!

The more queries you write for different use cases, the more intuitive writing queries will become. If you are looking for some awesome SQL practice exercises, check out our interactive SQL Practice Set. It offers 88 interactive exercises that range from simple to more advanced.

And if you are looking for a comprehensive learning experience, our SQL from A to Z track is for you. It offers 7 interactive courses and will set you on the path to pro-level SQL skills. Or check out this article on the various ways to learn SQL.

All the best and happy learning!