SQL ORDER BY

ORDER BY sorts query results before they’re returned. Without it, the database returns rows in an indeterminate order that can change between runs. If result order matters, ORDER BY is required.

Basic Syntax

SELECT columns
FROM table_name
ORDER BY column ASC;   -- ascending (A→Z, lowest to highest)
ORDER BY column DESC;  -- descending (Z→A, highest to lowest)

ASC is the default — you can omit it:

ORDER BY name        -- same as ORDER BY name ASC
ORDER BY price DESC  -- descending: most expensive first

Ascending vs Descending

-- Products alphabetically A → Z
SELECT name, category FROM products ORDER BY name ASC;

-- Most expensive first
SELECT name, price FROM products ORDER BY price DESC;

-- Most recent orders first
SELECT order_id, amount, created_at FROM orders ORDER BY created_at DESC;

-- Oldest first (chronological)
SELECT order_id, created_at FROM orders ORDER BY created_at ASC;

Multi-Column Sorting

Specify multiple columns for primary → secondary → tertiary sort:

-- Primary: category alphabetically
-- Secondary: within each category, most expensive first
SELECT name, category, price
FROM products
ORDER BY
    category ASC,
    price DESC;

Each column has its own direction independently:

-- Employees: by department (A→Z), then by hire date (oldest first), then salary (highest first)
SELECT name, department, hire_date, salary
FROM employees
ORDER BY
    department ASC,
    hire_date ASC,
    salary DESC;

ORDER BY Column Position

Reference by position number in the SELECT list (1-indexed):

SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY 2 DESC, 3 DESC;  -- sort by cnt, then avg_price

Position numbers are useful with long expressions but reduce readability — the number gives no hint of what’s being sorted.

ORDER BY Column Alias

Most databases allow sorting by an alias defined in SELECT:

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;  -- alias defined in SELECT

ORDER BY Expression

Sort on a computed value, not just a raw column:

-- Sort by computed revenue
SELECT order_id, quantity, unit_price
FROM order_items
ORDER BY quantity * unit_price DESC;

-- Sort by string length
SELECT name FROM products ORDER BY LENGTH(name);

-- Sort by age at signup (oldest first)
SELECT name, birth_date FROM users
ORDER BY EXTRACT(YEAR FROM AGE(NOW(), birth_date)) DESC;

Handling NULLs in ORDER BY

Default NULL sorting varies by database. Control it explicitly:

-- PostgreSQL: NULLs at the end regardless of direction
ORDER BY last_login DESC NULLS LAST
ORDER BY last_login ASC NULLS LAST

-- NULLs first
ORDER BY last_login DESC NULLS FIRST

-- MySQL / SQL Server workaround: push NULLs to end
ORDER BY
    CASE WHEN last_login IS NULL THEN 1 ELSE 0 END,
    last_login DESC;

Custom Sort Order with CASE WHEN

When default alphabetical or numeric order isn’t what you need:

-- Business-logic sort: show 'urgent' status first, then 'pending', then others
SELECT ticket_id, subject, status, created_at
FROM tickets
ORDER BY
    CASE status
        WHEN 'urgent'  THEN 1
        WHEN 'pending' THEN 2
        WHEN 'open'    THEN 3
        ELSE 4
    END ASC,
    created_at ASC;

ORDER BY with LIMIT (Top-N)

The most common pattern — get the top N rows by some criterion:

-- Top 10 products by revenue
SELECT product_id, name, total_revenue
FROM product_summary
ORDER BY total_revenue DESC
LIMIT 10;

-- 3 most recently updated records
SELECT id, name, updated_at FROM items
ORDER BY updated_at DESC
LIMIT 3;

-- 5 cheapest active products
SELECT name, price FROM products
WHERE is_active = TRUE
ORDER BY price ASC
LIMIT 5;

ORDER BY in Subqueries and CTEs

In most databases, ORDER BY in a subquery without LIMIT has no guaranteed effect on the outer query — the database may not preserve order between query layers. Sorting only matters in the final result:

-- Sorting in an outer query is meaningful
SELECT * FROM (
    SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY 1
) t
ORDER BY total DESC;

-- Sorting inside a CTE without LIMIT is ignored in the final output
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
    FROM scores
)
SELECT * FROM ranked WHERE rn <= 10 ORDER BY score DESC;

Practical Examples

Sales report sorted by month and then category:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    category,
    SUM(amount) AS revenue
FROM orders
JOIN products USING (product_id)
GROUP BY 1, 2
ORDER BY 1 DESC, revenue DESC;

Customer leaderboard:

SELECT
    customer_id,
    name,
    SUM(amount) AS lifetime_value,
    COUNT(*) AS order_count
FROM customers
JOIN orders USING (customer_id)
WHERE orders.status = 'completed'
GROUP BY customer_id, name
ORDER BY lifetime_value DESC
LIMIT 50;

Recently active users:

SELECT user_id, email, last_active_at
FROM users
WHERE last_active_at IS NOT NULL
ORDER BY last_active_at DESC NULLS LAST
LIMIT 100;