Chapter 21: PostgreSQL ORDER BY

Part 1: What is ORDER BY?

The ORDER BY clause sorts the result set of a query by one or more columns, in ascending or descending order. It’s the last step in query execution (conceptually), organizing your data after it’s been filtered and grouped.

The Basic Syntax

• ASC – Ascending order (smallest to largest, A to Z) – this is the default

• DESC – Descending order (largest to smallest, Z to A)

Part 2: Setting Up Our Example Data

Let’s create a rich dataset to explore all the sorting possibilities. We’ll create an online bookstore database:

Part 3: Basic Sorting – Single Column

1. Ascending Order (Default)

When you don’t specify a direction, PostgreSQL assumes ASC (ascending):

Result:

Notice how NULL prices are excluded because we used WHERE price IS NOT NULL. Without that, NULLs would appear at the end (we’ll discuss NULL sorting later).

2. Descending Order

Use DESC to sort from highest to lowest:

Result:

3. Sorting Text (Alphabetical)

Text sorting follows alphabetical order (based on the database’s collation):

Result:

Notice that numbers come before letters in ASCII sorting, so “1984” appears first.

4. Sorting by Date

Dates sort chronologically – older dates first in ascending order:

Result:

Result:

Part 4: Multi-Column Sorting

This is where ORDER BY becomes truly powerful. You can sort by multiple columns, with each column having its own sort direction.

1. Primary and Secondary Sorting

Sort by genre first, then by price within each genre:

Result (partial):

Notice how:

• All Biography books come first (alphabetically)

• Within Biography, books are sorted by price descending

• Then Children’s books, then Fantasy, etc.

2. Different Directions for Different Columns

You can mix ASC and DESC for different columns:

Result:

3. Using Column Position (Not Recommended)

Instead of column names, you can use numbers representing column positions in the SELECT list:

⚠️ Warning: This is fragile! If you change the SELECT list order, your sorting breaks. Always prefer explicit column names for clarity and maintainability.

Part 5: Sorting with NULL Values

NULL values present a special challenge in sorting. Where should they appear? PostgreSQL gives you control.

1. Default NULL Behavior

By default, NULLs are considered larger than any non-NULL value:

Result (with NULLs at the end):

In descending order, NULLs appear at the beginning:

Result:

2. Controlling NULL Placement with NULLS FIRST/LAST

PostgreSQL provides special clauses to control NULL positioning:

This is incredibly useful when you want to see missing values prominently or hide them at the end.

3. Practical Example: Inventory Report

Result:

Part 6: Sorting by Expressions

You’re not limited to column names – you can sort by any expression.

1. Sorting by Calculated Values

2. Sorting by String Functions

Result:

3. Sorting by Case-Insensitive Text

4. Complex Expressions

Result:

Part 7: Sorting with CASE Statements

For complex, conditional sorting logic, you can use CASE inside ORDER BY.

1. Custom Sort Order

Sort genres in a custom order, not alphabetical:

Result:

2. Priority Sorting

Show in-stock items first, then sort by price:

Result:

All in-stock items appear first (sorted by price), followed by out-of-stock items.

3. Multi-Tier Priority

Part 8: Random Sorting

Sometimes you want random order – for features like “random book recommendation”:

Each time you run this, you get a different set of 5 books.

Performance Note: ORDER BY RANDOM() on large tables can be slow because it must assign a random number to every row and then sort. For large datasets, consider alternative approaches.

Part 9: Sorting with DISTINCT

When using SELECT DISTINCT, sorting becomes particularly important:

Note: The columns in ORDER BY must appear in the SELECT list when using DISTINCT:

PostgreSQL is more permissive, but for portability, include all ORDER BY columns in SELECT when using DISTINCT.

Part 10: Sorting with GROUP BY and Aggregate Functions

When grouping data, you often want to sort by the aggregated results:

1. Sort by Count

Result:

2. Sort by Aggregate

3. Multiple Aggregates

Part 11: Real-World Examples

Example 1: E-commerce Product Listing

Example 2: Bestseller List

Example 3: Recently Published, Highly Rated Books

Example 4: Inventory Value Report

Example 5: Customer Orders Analysis

Let’s create an orders table for more examples:

Part 12: Performance Considerations

1. Indexes for Sorting

Indexes can dramatically speed up ORDER BY operations:

2. When Indexes Help

3. LIMIT and Sorting

When combining ORDER BY with LIMIT, PostgreSQL can optimize by only sorting enough rows to satisfy the LIMIT:

4. Sorting Large Datasets

For very large tables, consider:

• Creating appropriate indexes

• Using approximate techniques when exact order isn’t critical

• Partitioning tables

• Materialized views for frequently-used sorted results

Part 13: Advanced Sorting Techniques

1. Sorting by JSON Fields

If you have JSON data, you can sort by specific keys:

2. Natural Sort (Human-Friendly Sorting)

For strings with numbers, natural sorting often makes more sense:

3. Collation-Specific Sorting

Different languages sort differently:

Part 14: Common Mistakes and How to Avoid Them

Mistake 1: Forgetting ORDER BY Altogether

Mistake 2: Mixing ASC/DESC Incorrectly

Each column gets its own direction specification.

Mistake 3: Using Column Aliases Incorrectly

Mistake 4: Sorting by Large Text Columns

Mistake 5: Not Understanding NULL Behavior

Summary: The ORDER BY Philosophy

ORDER BY transforms raw data into meaningful information by revealing patterns, rankings, and relationships:

1. Basic sorting – Single column, ASC/DESC

2. Multi-level sorting – Primary, secondary, tertiary criteria

3. Expression sorting – Sort by calculated values

4. Conditional sorting – Custom logic with CASE

5. NULL handling – Control where missing values appear

6. Performance awareness – Indexes and query optimization

Remember the query execution order:

1. FROM (get tables)

2. WHERE (filter rows)

3. GROUP BY (group rows)

4. HAVING (filter groups)

5. SELECT (compute expressions)

6. ORDER BY (sort results) – HERE!

7. LIMIT (limit output)

This means ORDER BY works on the final result set, after all filtering and grouping. It’s the last step before LIMIT, which is why LIMIT can be optimized with sorted results.

Key takeaways:

• Always specify ORDER BY when row order matters – never rely on default order

• Use DESC for highest-to-lowest, ASC for lowest-to-highest

• Multi-column sorting is powerful – think of it as “then by” logic

• Control NULL placement explicitly with NULLS FIRST/LAST

• Index columns you frequently sort by

• Test with EXPLAIN to see if your sorts are efficient

The ORDER BY clause turns your database results from random piles of data into organized, meaningful information. Whether you’re building a user-facing product catalog, generating reports, or analyzing trends, mastering ORDER BY is essential for presenting data in its most useful form.

Would you like me to elaborate on any specific aspect of ORDER BY, such as more complex sorting scenarios, performance optimization, or integration with other SQL features?