intro
Find out everything you need to know about the SQL ORDER BY clause and learn different approaches to sorting result sets in your queries.
Data sorting affects the meaning of a result set. That’s why the SQL ORDER BY clause represents one of the most useful building blocks of the query definition world. This clause helps you sort records by the values in their columns.
In this article, you will learn what the ORDER BY SQL is, how it works, and how to use it in real-world examples.
Let's dive in!
What Is the SQL ORDER BY Clause?
The SQL ORDER BY clause sorts the result set of a SELECT statement in an ascending or descending order based on one or more columns. As an essential part of the SQL standard, pretty much all DBMS technologies support it. Find out more in the specific documentation pages:
Note that the order in which rows are returned in a result set is not guaranteed unless an ORDER BY SQL clause is specified. Thus, the same queries executed on the same data in different database systems may produce two differently sorted result sets. That is why it is so critical to know how to use ORDER BY in SQL.
ORDER BY SQL Syntax
This is what the syntax of the SQL ORDER BY clause looks like:
1
SELECT column_1, column_2, ..., column_n
2
FROM table_name
3
ORDER BY column_1 [ASC|DESC], column_2 [ASC|DESC], ..., conlun_n [ASC|DESC];
As you can see, the ORDER BY SQL keyword is followed by a list of columns. This must contain at least one column. Otherwise, you will get an SQL syntax error.
The sequence of the columns in the list defines how the result set will be sorted. In detail, the result set is sorted by the first column and then that ordered list is sorted by the second column, and so on.
The default sort order for each column is ascending, but you can specify that with the following two keywords:
Now, suppose you have a players table defined by the SQL DDL below:
1
CREATE TABLE players (
2
id INT NOT NULL AUTO_INCREMENT,
3
name VARCHAR(50),
4
birthdate DATE
5
goals INT,
6
PRIMARY KEY (id)
7
);
Consider this query:
1
SELECT name, goals
2
FROM players
3
ORDER BY goals;
This is equivalent to:
1
SELECT name, goals
2
FROM players
3
ORDER BY goals ASC;
In both cases, the result set will be sorted in ascending order according to the goals scored by the players.
Note that an ORDER BY clause can have more columns than the SELECT clause, as in the example below:
1
SELECT name
2
FROM players
3
ORDER BY name, goals;
The column names referenced within ORDER BY in SQL must correspond to either a column in the selected table or column alias in the SELECT list. Column names in the column list must be unique, which means the same column cannot appear twice in an ORDER BY clause.
In MySQL, SQL Server, PostgreSQL, and other DBMS technologies the sort column can also be specified as a non-negative integer. This represents the position of the column in the SELECT list. So, take a look at the query below:
1
SELECT name, goals
2
FROM players
3
ORDER BY goals DESC, name ASC;
That is equivalent to:
1
SELECT name, goals
2
FROM players
3
ORDER BY 2 DESC, 1 ASC;
SQL ORDER BY Clause: Use Cases and Examples
Now that you know how to use ORDER BY in SQL, you are ready to see it in action in some examples!
The sample queries below will be written in MySQL and executed in DbVisualizer, the database client with the highest user satisfaction in the market. Note that you can easily adapt them to any other SQL dialect and run them in your favorite client.
ORDER BY Descending
Suppose you want to retrieve the top scorers from a subset of NBA players. Here is how you can do it with a simple ORDER BY SQL query:
1
SELECT name, points
2
FROM players
3
ORDER BY points DESC;
The result will be:
Note that the result set is ordered by points as desired, from largest to smallest.
ORDER BY Alphabetically
Assume you need to sort a subset of NBA players alphabetically. That is as simple as writing a query with the SQL ORDER BY clause below:
1
SELECT name
2
FROM players
3
ORDER BY name ASC;
The result will be:
Note how ORDER BY works with both numeric and textual data. With numeric data, it orders from smallest to largest. With textual data, it sorts alphabetically.
ORDER BY Multiple Columns
Consider a scenario where you want to retrieve the list of employees sorted by salary according to the department they work in. This is how you can achieve that:
1
SELECT first_name, last_name, department, salary
2
FROM employees
3
ORDER BY department ASC, salary DESC;
The resulting dataset will be:
Note the sorting for both department and salary
This example of SQL ORDER BY multiple columns shows how it is possible to order a result set in an advanced way.
ORDER BY Date
Ordering by date is one of the most common use cases when it comes to sorting data. Some programming languages struggle with dates because they treat dates as regular strings. In SQL, you can sort by date simply by placing a date-type column in the ORDER BY clause:
1
SELECT name, birthdate AS date
2
FROM players
3
ORDER BY date;
The result set will show the players from the oldest to the youngest:
This example also shows how to sort the data using alias columns.
Best Practices of Using ORDER BY in SQL
Here is a list of tips and tricks you should keep in mind when using ORDER BY in SQL:
Conclusion
In this guide you have understood what the SQL ORDER BY clause is and how it works. Now you know how to use ORDER BY to sort the result sets of your SELECT queries. Thanks to the examples shown here, you have also seen how to sort data in SQL in many scenarios.
Sorting data in SQL takes computational resources and can slow down significantly your queries. Dig into that with a comprehensive database client with advanced query optimization features such as DbVisualizer! This is just one of the many features supported by the tool. Try DbVisualizer for free today!
FAQ
Can SQL ORDER BY work with multiple columns?
Yes, ORDER BY in SQL can work with multiple columns. You can specify multiple columns in the ORDER BY clause to sort the result set based on multiple criteria. The DBMS will apply them from left to right.
Is it possible to use ORDER BY to sort alphabetically in SQL?
Yes, it is possible to use ORDER BY to sort alphabetically. By default, ORDER BY sorts text data alphabetically in ascending order (from A to Z). To achieve alphabetical sorting, you only need to sort the desired column containing text data.
What are the performance implications of the ORDER BY SQL clause?
The ORDER BY SQL clause does have performance implications. Sorting the result set according to specified criteria can take a lot of time, especially on large datasets or when there are many sort conditions. To mitigate performance issues, you must define appropriate indexes on the columns used in the ORDER BY clause or ensure that the clause won’t sift through many rows at once using other measures.
Are all SQL data types orderable?
No, not all SQL data types are orderable in ORDER BY. While most data types like strings, numbers, and dates are orderable, some complex types like the BLOB data type may not have a natural order. You can still specify columns with these types in the ORDER BY column list, but the result may not be predictable.
How do different DBMSs sort NULL values?
Each DBMS has its own strategy to sort NULL values when specifying an ORDER BY clause:
| MySQL | PostgreSQL | SQL Server | Oracle | |
|---|---|---|---|---|
| Ascending order (ASC) | NULLs first | NULLs last | NULLs first | NULLs last |
| Descending order (DESC) | NULLs last | NULLs first | NULLs last | NULLs first |
Some database technologies, such as PostgreSQL, also support the following two options that can be set at the end of an ORDER BY SQL clause:
