Sorting Data in SQL: A Guide to the SQL ORDER BY Statement

When you need to organize query results in SQL, the SQL ORDER BY statement is the tool for the job. Whether you’re sorting products by price, arranging employees by department and salary, or displaying transactions in chronological order, this statement helps you retrieve data in the order you need. In this guide, I’ll show you how to sort data using the ORDER BY statement, including sorting by multiple fields and columns.

By the end of this post, you’ll know how to use the SQL ORDER BY statement effectively, whether it’s for simple or complex queries.

What is the SQL ORDER BY Statement?

The SQL ORDER BY statement is used to sort query results based on one or more columns or expressions. It lets you define whether the data should be sorted in ascending or descending order.

Here’s the basic syntax of the ORDER BY statement:

SELECT column1, column2  
FROM table_name  
ORDER BY column1 [ASC|DESC]

• ASC (ascending order): Default sorting order (e.g., A-Z, smallest to largest).
• DESC (descending order): Reverses the default order (e.g., Z-A, largest to smallest).

Sorting by a Single Column

To start, let’s look at a simple example where we sort data by a single column. For instance, if you want to sort employees alphabetically by their last name:

SELECT FirstName, LastName  
FROM Employees 
ORDER BY LastName

Here is what it looks like in SSMS:

Since ASC is the default, you don’t need to specify it. If you prefer reverse order, use DESC:

SELECT FirstName, LastName  
FROM Employees 
ORDER BY LastName DESC

Sorting by Multiple Columns

Sorting by a single column is straightforward, but what if you need more control over the order? That’s where sorting by multiple columns (or fields) becomes useful.

For example, let’s say you want to sort employees first by country (alphabetically) and then by birth date (newest to oldest):

SELECT FirstName, LastName, Country, BirthDate
FROM Employees  
ORDER BY Country ASC, BirthDate DESC

Here is what the results look like:

This query achieves the following:

1. Sorts all employees by country in ascending order (A-Z).
2. Within each department, sorts employees by birth date in descending order (newest to oldest).

This flexibility makes the SQL ORDER BY multiple fields functionality incredibly powerful for detailed data presentation.

Using ORDER BY with Calculations or Expressions

The SQL ORDER BY statement also supports sorting based on calculated values or expressions. This is particularly useful when you want to order results by a derived column that doesn’t exist in the table.

For example, let’s take all the records from the Products table, and calculate the value of the available stock (UnitsInStock * UnitPrice), and sort the results by this value:

SELECT ProductName, UnitPrice, UnitsInStock, UnitsInStock * UnitPrice AS StockValue 
FROM Products
ORDER BY StockValue DESC

The alias StockValue is used in the ORDER BY clause to sort the rows by the computed value from highest to lowest.

Handling NULL Values with ORDER BY

When sorting, you may encounter NULL values in your dataset. By default:

• In ascending order, NULL values appear first.
• In descending order, NULL values appear last.

For instance, if you want the employees with the NULL value in the Region field to appear at the end of the record set, you should write the following query:

SELECT *
FROM Employees
ORDER BY Region DESC

This query ensures that rows with NULL values appear last.

Conclusion

The SQL ORDER BY statement is an essential tool for sorting query results in a way that makes them more meaningful and usable. Whether you’re sorting by a single column, ordering by multiple columns, or using calculated expressions, mastering this statement will significantly improve the clarity and utility of your SQL queries.

Start experimenting with SQL ORDER BY multiple fields in your own datasets to see its full potential in action. Have questions or additional tips to share? Drop them in the comments!

Also, make sure you check my last post about SQL called Mastering the SQL WHERE Clause: A Free 20-Minute Guide to Filtering Data.

If you would like to learn more about SQL, check out this amazing book on Amazon.