SQL ORDER BY

ORDER BY sorts query results. Without it, the database can return rows in any order — and that order can change between runs. If your output needs to be in a specific sequence, ORDER BY makes that deterministic.

Basic Syntax

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;     -- ASC = ascending (default)
ORDER BY column1 DESC;    -- DESC = descending

ASC is the default and can be omitted:

-- These are equivalent
ORDER BY name ASC
ORDER BY name

Sorting Direction

-- Alphabetical A to Z
SELECT name, category FROM products ORDER BY name ASC;

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

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

-- Oldest to newest
SELECT order_id, created_at FROM orders ORDER BY created_at ASC;

Multi-Column Sorting

Specify multiple columns to sort by primary, secondary, and further criteria:

-- Sort by category A→Z, then by price most-expensive-first within each category
SELECT name, category, price
FROM products
ORDER BY
    category ASC,
    price DESC;

The database sorts by the first column, then uses subsequent columns to break ties. Direction (ASC/DESC) is specified per column independently.

-- Employees: by department alphabetically, then by hire date oldest first
SELECT name, department, hire_date, salary
FROM employees
ORDER BY department ASC, hire_date ASC;

-- Orders: by status, then by amount descending within each status
SELECT order_id, status, amount
FROM orders
ORDER BY status ASC, amount DESC;

Sorting by Column Position

You can reference columns by their position in the SELECT list:

-- Sort by first column (category), then second column (price)
SELECT category, price, name
FROM products
ORDER BY 1 ASC, 2 DESC;

This is useful in queries with long expressions but reduces readability — the position number gives no indication of what’s being sorted.

Sorting by Expressions

Sort on computed values, not just raw columns:

-- Sort by total order value (computed expression)
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) ASC;

-- Sort by extracted date part (month of year, ignoring which year)
SELECT name, birthday
FROM customers
ORDER BY EXTRACT(MONTH FROM birthday), EXTRACT(DAY FROM birthday);

Sorting by Column Alias

In most databases, you can sort by a column alias defined in SELECT:

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

This doesn’t work in all databases — PostgreSQL and MySQL support it, but strict SQL Standard compliance requires the full expression.

NULL Handling in ORDER BY

By default, NULL values sort differently depending on the database:

PostgreSQL: NULLs sort LAST in ASC, FIRST in DESC
MySQL: NULLs sort first in both ASC and DESC
SQL Server / Oracle: NULLs sort last in ASC, first in DESC

Control NULL ordering explicitly:

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

-- Put NULLs first
ORDER BY last_login DESC NULLS FIRST;

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

ORDER BY with LIMIT (Top-N Queries)

ORDER BY combined with LIMIT is how you get top-N results:

-- Top 5 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- 10 most recent orders
SELECT order_id, customer_id, amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 10;

-- Least expensive item in each category
WITH ranked AS (
    SELECT
        name,
        category,
        price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price ASC) AS rn
    FROM products
)
SELECT name, category, price
FROM ranked
WHERE rn = 1;

CASE WHEN in ORDER BY for Custom Sort Order

When alphabetical order doesn’t match desired display order:

-- Display status in a specific business-logic order
SELECT order_id, status, amount
FROM orders
ORDER BY
    CASE status
        WHEN 'pending'    THEN 1
        WHEN 'processing' THEN 2
        WHEN 'shipped'    THEN 3
        WHEN 'completed'  THEN 4
        WHEN 'cancelled'  THEN 5
        ELSE 6
    END ASC,
    created_at DESC;

ORDER BY Position in a Query

ORDER BY is evaluated last in the SQL execution order:

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

This means ORDER BY can reference aliases defined in SELECT (in databases that support it) and sees the post-aggregation result when used with GROUP BY.

Practical Examples

Customers by most recent activity:

SELECT customer_id, name, last_order_date
FROM customers
WHERE is_active = TRUE
ORDER BY last_order_date DESC NULLS LAST
LIMIT 50;

Leaderboard: top 10 users by score:

SELECT
    user_id,
    username,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM user_scores
ORDER BY score DESC
LIMIT 10;

Products sorted by discount percentage:

SELECT
    name,
    original_price,
    sale_price,
    ROUND(100.0 * (original_price - sale_price) / original_price, 1) AS discount_pct
FROM products
WHERE on_sale = TRUE
ORDER BY discount_pct DESC;