SQL ORDER BY: Ascending, Descending, Multi-Column Sorting, and Where NULLs Land

ORDER BY controls the presentation order of a result. It does not change which rows qualify, and without it PostgreSQL does not promise a reusable row order.

A reliable sort key answers three questions: which expressions are compared, which direction applies to each expression, and what breaks ties. Add an explicit NULL policy whenever a nullable expression participates.

Build a complete sort key

Each direction applies only to the expression beside it. This query sorts known amounts from high to low, then uses order date and order ID to make ties deterministic. NULL amounts come last by policy.

Input: orders

SELECT
  order_id,
  amount,
  order_date
FROM orders
ORDER BY
  amount DESC NULLS LAST,
  order_date ASC,
  order_id ASC;

Output

ASC is the default direction, but writing it can make a mixed-direction key easier to review. The clause runs late enough to reference a select-list alias; the full sequence is in SQL order of operations.

Place NULLs deliberately

PostgreSQL supports NULLS FIRST and NULLS LAST on each sort expression. Here unshipped rows form the first group, and order ID fixes their internal order.

Input: orders

SELECT
  order_id,
  shipped_at
FROM orders
ORDER BY
  shipped_at ASC NULLS FIRST,
  order_id ASC;

Output

Top N and pagination need stable ties

LIMIT 3 means three rows, not three distinct amounts. All three highest rows happen to tie at 250, and order ID decides which row would come first within that tie.

Input: orders

SELECT
  order_id,
  amount
FROM orders
WHERE amount IS NOT NULL
ORDER BY amount DESC, order_id ASC
LIMIT 3;

Output

Keyset pagination resumes after the full last-seen key. The cursor says the previous page ended at amount 250 and order ID 3, so the next page begins with order 8 and then moves to amount 120.

Input: page_cursor

Input: orders

SELECT
  o.order_id,
  o.amount
FROM orders AS o
CROSS JOIN page_cursor AS c
WHERE o.amount IS NOT NULL
  AND (
    o.amount < c.last_amount
    OR (
      o.amount = c.last_amount
      AND o.order_id > c.last_order_id
    )
  )
ORDER BY o.amount DESC, o.order_id ASC
LIMIT 3;

Output

The predicate assumes immutable sort keys while pages are consumed. If amount changes between requests, pagination semantics need a snapshot or another stability policy.

Sorting rows and ranking ties are different

ORDER BY sequences rows. RANK assigns equal ranks to equal amounts, which is useful when the business question cares about tied levels. The next distinct amount starts at rank 4 because three rows share rank 1.

Input: orders

SELECT
  order_id,
  amount,
  RANK() OVER (
    ORDER BY amount DESC NULLS LAST
  ) AS amount_rank
FROM orders
ORDER BY amount_rank, order_id;

Output

Use SQL DISTINCT when the required output is unique values rather than ranked rows. Window ranking choices are developed in the window-functions guide.

Window order and final order have separate jobs

The window order below computes revenue in chronological order. The final ORDER BY then presents the largest cumulative values first. Changing the final order would not recompute the running totals.

Input: orders

SELECT
  order_id,
  order_date,
  amount,
  SUM(COALESCE(amount, 0)) OVER (
    ORDER BY order_date, order_id
    ROWS UNBOUNDED PRECEDING
  ) AS running_revenue
FROM orders
ORDER BY running_revenue DESC, order_id;

Output

Business categories need a stated order rather than alphabetic coincidence. A CASE expression maps each status to a sort position; order ID resolves rows within a status.

Input: orders

SELECT
  order_id,
  status
FROM orders
ORDER BY
  CASE status
    WHEN 'processing' THEN 1
    WHEN 'pending' THEN 2
    WHEN 'completed' THEN 3
    WHEN 'cancelled' THEN 4
    ELSE 5
  END,
  order_id;

Output

The SQL CASE guide covers condition ordering and unmatched values.

FAQ

Is row order guaranteed without ORDER BY?

No reusable ordering contract exists without an outer ORDER BY. A plan, index, or small fixture can make results look stable without promising that order for another execution.

Does DESC apply to every later column?

No. Direction belongs to one expression. Write the direction beside every expression when a key mixes ascending and descending order.

How should NULL values be sorted?

Choose the business policy and state it with NULLS FIRST or NULLS LAST in PostgreSQL. Add another expression when multiple NULL rows need deterministic order.

Why does LIMIT need a tie breaker?

If rows compare equal on the listed key, any of them can occupy the cutoff position. A stable unique tie breaker makes selection and pagination repeatable for unchanged data.

Is ORDER BY inside OVER the final output order?

No. It defines the sequence used by that window calculation. The outer query's ORDER BY controls displayed row order.