ORDER BY Purpose

Technical Explanation

ORDER BY sorts your result set by one or more columns in ascending (ASC) or descending (DESC) order. It's the last clause to execute in a query's logical order.

Syntax

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

Key Points

Default is ASC — If you don't specify, SQL assumes ascending
NULL ordering — NULLs typically come first in ASC, last in DESC (database-specific)
Multiple columns — Sort by the first column, then break ties with the second
Can use aliases — You can ORDER BY a column alias defined in SELECT
Expression ordering — You can ORDER BY a calculation or function

Logical Execution Order

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY    ← Last to execute

Code Examples

Using the CatCafe dataset:

-- Table: orders
-- id, customer_id, cat_id, order_date, total_amount, status

-- Basic ascending order (default)
SELECT
    customer_id,
    total_amount,
    order_date
FROM orders
ORDER BY order_date ASC;  -- Oldest first

-- Descending order
SELECT
    name,
    total_spent
FROM customers
ORDER BY total_spent DESC;  -- Highest spenders first

-- Multiple columns: Sort by city, then by name within city
SELECT
    city,
    name,
    total_spent
FROM customers
ORDER BY city ASC, name ASC;

-- Order by expression: Customers by lifetime value (total_spent + 10% bonus)
SELECT
    name,
    total_spent,
    total_spent * 1.1 as lifetime_value
FROM customers
ORDER BY lifetime_value DESC;

-- Order with NULL handling
SELECT
    name,
    phone,
    email
FROM customers
ORDER BY phone DESC NULLS LAST;  -- Customers with phones first (PostgreSQL)

-- Combining with WHERE and GROUP BY
SELECT
    city,
    COUNT(*) as customer_count,
    AVG(total_spent) as avg_spent
FROM customers
WHERE registration_date >= '2024-01-01'
GROUP BY city
HAVING COUNT(*) > 5
ORDER BY avg_spent DESC
LIMIT 5;
-- Top 5 cities by average spending (2024 customers only, min 5 customers)

-- Order by position number
SELECT
    name,
    city,
    total_spent
FROM customers
ORDER BY 2, 3 DESC;  -- Order by 2nd column, then 3rd column

The Cat Analogy

Imagine organizing cats in a cage room:

ORDER BY age ASC = Kittens first, then older cats

Mochi (2mo) → Luna (3mo) → Whiskers (5yr) → Shadow (8yr)

ORDER BY age DESC = Senior cats first

Shadow (8yr) → Whiskers (5yr) → Luna (3mo) → Mochi (2mo)

ORDER BY weight DESC, age ASC = Heaviest first, then youngest among equal weights:

Shadow (12lb, 8yr) → Oliver (12lb, 3yr) → Luna (8lb, 3mo) → Mochi (5lb, 2mo)

The cat's cage position doesn't change their actual attributes—just how you view them.

Common Pitfalls

Forgetting ORDER BY for Deterministic Results

-- Without ORDER BY, you have NO guaranteed order
SELECT id, name FROM customers;  -- Order not guaranteed!

-- Always use ORDER BY when order matters
SELECT id, name FROM customers ORDER BY id;

ORDER BY After LIMIT (Top-N Queries)

-- Get top 5 customers by spending
SELECT
    name,
    total_spent
FROM customers
ORDER BY total_spent DESC
LIMIT 5;
-- Crucial: Without ORDER BY, LIMIT just returns 5 arbitrary rows!

Sorting by Non-Selected Columns

-- Valid but confusing: ORDER BY a column not in SELECT
SELECT name, city
FROM customers
ORDER BY registration_date;
-- Works but readers can't see why order is what it is

Case Sensitivity in Sorting

-- Default: Often case-insensitive, but depends on database collation
-- Explicit case-sensitive order (PostgreSQL):
SELECT name
FROM customers
ORDER BY name COLLATE "C";  -- ASCII order

-- Case-insensitive with LOWER:
SELECT name
FROM customers
ORDER BY LOWER(name);

Exercises

Exercise 1

Write a query to get the top 3 cats by number of orders, showing cat name, breed, and order count.

Exercise 2

Explain why this query might not give consistent results:

SELECT id, name FROM customers WHERE city = 'Brooklyn';

Exercise 3

Write a query showing customers sorted by:

City (alphabetically)
Within each city, by total_spent (highest first)
Within each city/spending tier, by name (alphabetically)

Exercise 4

Write a query to get the most recent order per customer using ORDER BY and a subquery pattern.

Exercise 5

What does this query return? Is the order deterministic?

SELECT DISTINCT city FROM customers ORDER BY city;

Key Takeaways

ORDER BY is the last clause to execute logically
ASC (ascending) is the default; NULLs typically come first in ASC
Use DESC for descending order; NULLS LAST to push NULLs to the end
Multiple columns: first column is primary sort, second breaks ties
Always use ORDER BY when order matters (pagination, top-N, rankings)
Without ORDER BY, result set order is non-deterministic
Can ORDER BY expressions, aliases, or column positions (though positions are less clear)