ORDER BY DESC in Postgres: NULLs, ties and indexes
Sort newest first in Postgres without surprises: where NULLs land, why you need a tie-breaker, how one index serves both directions, and keyset pagination.
ORDER BY column DESC sorts a result from the highest value to the lowest: newest first for timestamps, largest first for numbers, Z to A for text. Ascending is the default, so DESC is the word you add for every "latest orders" or "top customers" list.
SELECT id, customer_id, total_cents, placed_at
FROM orders
ORDER BY placed_at DESC
LIMIT 20;
That is the whole feature, and it is also where several quiet bugs start: rows that swap places between page loads, NULL values at the top of the list, and a sort that reads the whole table. This article covers each of them in PostgreSQL, which is what every Scribase project runs.
The table
CREATE TABLE orders (
id bigint PRIMARY KEY,
customer_id bigint NOT NULL,
status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
total_cents integer NOT NULL CHECK (total_cents >= 0),
placed_at timestamptz NOT NULL DEFAULT now(),
shipped_at timestamptz
);
shipped_at is nullable on purpose. An order that has not shipped yet has no shipping time.
Without ORDER BY there is no order
A query with no ORDER BY returns rows in whatever order the plan produces. On a small table that often looks like insertion order, which is why the bug survives testing. After an update, a vacuum, or a different plan, the order changes. If the order matters to anyone, write the ORDER BY.
DESC applies to one column at a time
Each sort key has its own direction. DESC covers only the key it follows.
SELECT id, status, total_cents
FROM orders
ORDER BY status, total_cents DESC;
This sorts by status ascending, and within each status by total_cents descending. To reverse both, write DESC twice.
You can also sort by an expression, by an output alias, or by position:
SELECT customer_id, sum(total_cents) AS spent_cents
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY spent_cents DESC
LIMIT 10;
ORDER BY 2 DESC would do the same. Positions save typing and break when someone reorders the select list, so prefer the alias.
Where NULLs go
In Postgres a NULL sorts as if it were larger than every other value. Ascending order puts NULL last. Descending order puts NULL first.
-- Unshipped orders (shipped_at IS NULL) come back at the top.
SELECT id, shipped_at
FROM orders
ORDER BY shipped_at DESC;
For "most recently shipped" that is almost never what you want. Say where the NULL rows belong:
SELECT id, shipped_at
FROM orders
ORDER BY shipped_at DESC NULLS LAST;
NULLS FIRST and NULLS LAST work with either direction. Other databases place NULL differently by default, so SQL that was written for one and run on another is a common source of this bug.
Add a tie-breaker, or pages will overlap
Two orders can share the same placed_at. When they do, ORDER BY placed_at DESC does not say which comes first, and Postgres is free to return them in either order from one query to the next. With LIMIT and OFFSET, the same row can then show up on page 1 and again on page 2, while another row is never shown.
The fix is a sort key that is unique. Add the primary key as the last column:
SELECT id, customer_id, total_cents, placed_at
FROM orders
ORDER BY placed_at DESC, id DESC
LIMIT 20;
Now every row has exactly one position. Any unique column works. The primary key is the usual choice because it is already indexed and never NULL.
One index serves both directions
A B-tree index can be read forwards or backwards. An ordinary index on placed_at already serves ORDER BY placed_at DESC: Postgres walks it from the end.
CREATE INDEX orders_placed_at_id_idx ON orders (placed_at, id);
That index covers ORDER BY placed_at, id and ORDER BY placed_at DESC, id DESC, because the second is the exact reverse of the first. With a LIMIT 20, Postgres reads twenty index entries and stops, instead of sorting the table.
You need DESC in the index definition only when the directions are mixed:
-- For ORDER BY status ASC, total_cents DESC
CREATE INDEX orders_status_total_idx ON orders (status, total_cents DESC);
The same goes for NULLS LAST. A plain index on shipped_at, read backwards, yields NULL rows first. To serve ORDER BY shipped_at DESC NULLS LAST from an index, declare it that way:
CREATE INDEX orders_shipped_at_idx ON orders (shipped_at DESC NULLS LAST);
Check the plan with EXPLAIN (ANALYZE, BUFFERS). An Index Scan Backward or an Index Scan with no Sort node above it means the index did the ordering. A Sort node with Sort Method: top-N heapsort means Postgres read every matching row and kept the top few, which is fine on a small table and slow on a large one.
Keyset pagination: the next page without OFFSET
OFFSET 10000 makes Postgres produce ten thousand rows and throw them away. With a stable descending order you can ask for "the rows after the last one I saw" instead:
SELECT id, customer_id, total_cents, placed_at
FROM orders
WHERE (placed_at, id) < ('2026-11-17 09:30:00+00', 48211)
ORDER BY placed_at DESC, id DESC
LIMIT 20;
The two values are the placed_at and id of the last row on the previous page. The row comparison (placed_at, id) < (...) matches the sort order exactly, so the (placed_at, id) index above finds the starting point directly. Page 500 costs the same as page 1, and rows inserted while someone is paging do not shift the pages.
This only works when the sort key is unique, which is one more reason for the tie-breaker.
The latest row per group
"The most recent order for each customer" is a descending sort with a Postgres shortcut:
SELECT DISTINCT ON (customer_id)
customer_id, id, total_cents, placed_at
FROM orders
ORDER BY customer_id, placed_at DESC, id DESC;
DISTINCT ON (customer_id) keeps the first row for each customer, and the ORDER BY decides which row is first. The leading sort key must match the DISTINCT ON expression.
Trying the index on a Scribase branch
Whether a sort uses an index depends on the data: how many rows, how selective the WHERE clause is, how many values are NULL. A plan from ten seed rows tells you nothing about the plan in production, and building an index on a busy production table to find out is the expensive way to learn.
Scribase gives every project copy-on-write Postgres branches. A branch is a full, writable Postgres that starts as a copy of another database without copying every page first. Writes on the branch stay on the branch. Branches and preview environments are included in the project price, on the free project too.
- Create a preview environment. The default data mode is
sanitized: a copy of the parent with configured columns anonymized, so row counts and value distribution stay realistic. Usesnapshotonly when you truly need exact rows. - Run the query with
EXPLAIN (ANALYZE, BUFFERS)and note theSortnode. - Create the index on the branch and run it again.
- If the plan improved, review
scribase branch diffand dry-run the merge. Merges carry schema changes such as the new index. They do not copy the branch's rows back. - Delete the preview, or let its TTL remove it.
The full loop, including the commands, is in branch per task. The mechanics of branches are on the Postgres branching page. For a quick local try, with no account:
scribase branch new try-order-by
psql "$(scribase branch url try-order-by)"
scribase branch delete try-order-by
Common mistakes
- Relying on the order of a query that has no
ORDER BY. - Sorting by a column that is not unique and paginating with
OFFSET, so rows repeat or go missing. - Forgetting that
DESCputsNULLfirst in Postgres. - Writing
ORDER BY a, b DESCand expecting both columns to be descending. - Adding a separate descending index when the existing ascending one already serves the query backwards.
What to read next
UNION and UNION ALL covers sorting a combined result, where ORDER BY belongs at the very end. SQL subquery and common table expressions cover the queries you will most often be sorting. The product guides are in the Scribase guides.
To try the examples on a hosted project, start at scribase.com. One project is free, with no card and no time limit. A temporary project via npx scribase init --temp needs no account.