# 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.

Source: https://scribase.com/blog/desc-order

By Scribase, 2026-11-18.

`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.

```sql
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

```sql
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.

```sql
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:

```sql
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.

```sql
-- 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:

```sql
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:

```sql
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.

```sql
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:

```sql
-- 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:

```sql
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:

```sql
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:

```sql
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.

1. 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. Use `snapshot` only when you truly need exact rows.
2. Run the query with `EXPLAIN (ANALYZE, BUFFERS)` and note the `Sort` node.
3. Create the index on the branch and run it again.
4. If the plan improved, review `scribase branch diff` and dry-run the merge. Merges carry schema changes such as the new index. They do not copy the branch's rows back.
5. Delete the preview, or let its TTL remove it.

The full loop, including the commands, is in [branch per task](https://docs.scribase.com/docs/agents/branch-per-task). The mechanics of branches are on the [Postgres branching](/postgres-branching) page. For a quick local try, with no account:

```sh
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 `DESC` puts `NULL` first in Postgres.
- Writing `ORDER BY a, b DESC` and 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](/blog/sql-union) covers sorting a combined result, where `ORDER BY` belongs at the very end. [SQL subquery](/blog/sql-subquery) and [common table expressions](/blog/common-table-expression) cover the queries you will most often be sorting. The product guides are in the [Scribase guides](/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.