MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL ORDER BY Clause: Definitive Guide

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

Copy
        
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:

  • ASC: SQL ORDER BY ascending behavior (smallest values first)
  • DESC: SQL ORDER BY descending behavior (largest values first)

Now, suppose you have a players table defined by the SQL DDL below:

Copy
        
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:

Copy
        
1 SELECT name, goals 2 FROM players 3 ORDER BY goals;

This is equivalent to:

Copy
        
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:

Copy
        
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:

Copy
        
1 SELECT name, goals 2 FROM players 3 ORDER BY goals DESC, name ASC;

That is equivalent to:

Copy
        
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:

Copy
        
1 SELECT name, points 2 FROM players 3 ORDER BY points DESC;

The result will be:

Executing the ORDER BY query in DbVisualizer
Executing the ORDER BY query in DbVisualizer

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:

Copy
        
1 SELECT name 2 FROM players 3 ORDER BY name ASC;

The result will be:

Note the alphabetical sorting
Note the alphabetical sorting

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:

Copy
        
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
Note the sorting for both department and salary

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:

Copy
        
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:

Note that the records are ordered by date
Note that the records are ordered by date

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:

  • Specify ASC even if it is implicit to make your queries easier to read.
  • To reduce processing overhead, include only the columns needed for sorting.
  • Define an index with the same columns and in the same order as the ORDER BY clause to improve query performance.
  • Avoid sorting large datasets unless strictly necessary.
  • Monitor query execution times and resource utilization to identify potential bottlenecks related to sorting operations.
  • Understand how NULL values are handled during sorting in your specific DBMS.

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:

MySQLPostgreSQLSQL ServerOracle
Ascending order (ASC)NULLs firstNULLs lastNULLs firstNULLs last
Descending order (DESC)NULLs lastNULLs firstNULLs lastNULLs 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:

  • NULLS FIRST: NULLs should appear before non-NULL values
  • NULLS LAST NULLs should appear after non-NULL values

Check out our guide to learn more about how to deal with NULL values using SQL COALESCE.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30
title

SQL HAVING Clause: The Ultimate Guide

author Antonello Zanini tags SQL 7 min 2024-05-27
title

SQL INTERSECT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 2 min 2024-05-23
title

SQL Formatter: Definition and Best Tools

author Antonello Zanini tags SQL 9 min 2024-05-20
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CONVERT: The Handbook of Data Conversion in SQL

author Antonello Zanini tags CONVERT Data Conversion MySQL SQL SERVER 7 min 2024-05-02
title

SQL CASE Statement: Definitive Guide

author Antonello Zanini tags CASE ORACLE POSTGRESQL SQL SQL SERVER 5 min 2024-04-29

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗