Sorting Query Results with ORDER BY

The Basics: ORDER BY, ASC, and DESC

The ORDER BY clause is used to sort the rows in your query's result set. It's one of the last clauses in a SELECT statement. You can specify one or more columns to sort by, and for each column, you can define the direction of the sort.

• ASC (Ascending): Sorts from lowest to highest value (A to Z, 0 to 9). This is the default behavior if you don't specify a direction.
• DESC (Descending): Sorts from highest to lowest value (Z to A, 9 to 0).

Let's begin by reviewing the core syntax and seeing some simple examples.

As you've read, a basic query to get all customers sorted by name would look like this:

-- Get all customers, sorted alphabetically by name
SELECT * 
FROM CUSTOMERS 
ORDER BY NAME ASC;

-- Since ASC is the default, this is equivalent:
SELECT * 
FROM CUSTOMERS 
ORDER BY NAME;

To find the customers with the highest salary first, you would use DESC:

-- Get all customers, with the highest salary shown first
SELECT * 
FROM CUSTOMERS 
ORDER BY SALARY DESC;

Logical Query Execution: The "Why" Behind ORDER BY

Here’s a question that gets to the heart of how SQL engines work: when does the database actually perform the sort? Does it happen before or after it filters rows with WHERE? And does it happen before or after it calculates the columns in your SELECT list?

The answer lies in the logical order of execution, which is often different from the order in which you write the clauses.

This late execution of ORDER BY has a very important practical implication. Since it runs after the SELECT clause, you can sort your results by a column alias that you created in the SELECT list.

This video provides an excellent logical proof of this concept.

Understanding this execution order (FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY) is a big step towards thinking like the database engine. It helps you understand what's possible in a query and provides a foundation for diagnosing performance issues later on.

Sorting by Multiple Columns

Often, sorting by a single column isn't enough. You might want to sort a list of employees by department, and then, within each department, sort them by last name. This is called a hierarchical sort, and it's done by providing a comma-separated list of columns to the ORDER BY clause.

The database sorts the result set by the first column specified. Then, for any rows that have the same value in that first column, it sorts them by the second column, and so on.

Here is a static example of the same concept in action.

You can also specify a different sort direction for each column. For example, you might want to see product categories alphabetically (ASC), but within each category, you want to see the most expensive products first (DESC).

SELECT
    category,
    product_name,
    price
FROM products
ORDER BY category ASC, price DESC;

To solidify this, let's return to our reading resource.

SELECT
    priority,
    created_at,
    summary
FROM tickets
ORDER BY priority ASC, created_at ASC;

How Sorting Interacts with NULL

In our last lesson, we saw how NULL represents an "unknown" and requires special handling with IS NULL. How does ORDER BY treat these unknown values?

Unfortunately, the SQL standard does not strictly define a default sort order for NULLs. This is an important detail for job readiness, as the behavior can change depending on the database system you're using:

• PostgreSQL and Oracle: By default, NULL values are treated as the largest possible value. They appear last in ASC sorts and first in DESC sorts.
• MySQL and SQL Server: By default, NULL values are treated as the smallest possible value. They appear first in ASC sorts and last in DESC sorts.

While some database systems provide syntax like NULLS FIRST or NULLS LAST to explicitly control this, it's crucial to be aware of the default behavior for the system you are working with to avoid surprises.

Conclusion

Great work! You can now not only select and filter data but also present it in a logical, ordered way that meets business requirements.

Key Takeaways:

• The ORDER BY clause sorts your final result set. Use ASC for ascending order (the default) and DESC for descending order.
• ORDER BY is one of the last clauses to be logically executed, running after the SELECT clause. This allows you to sort by column aliases.
• You can sort by multiple columns to create a hierarchical sort order. The directions (ASC/DESC) can be set independently for each column.
• The default sorting behavior for NULL values varies between different database systems, which is an important consideration in a professional environment.

Preview of the Next Lesson:
You can now retrieve filtered data in a specific order. But what if you only need the "top 5" highest-scoring players, or you need to display search results on a web page, 20 at a time? In our next lesson, we will learn how to use LIMIT and OFFSET to select a specific slice of your sorted results, a technique essential for features like leaderboards and pagination.