NULLs first in DESC order

Severity: Info

When you write ORDER BY x DESC, PostgreSQL places NULL values first — before any non-null value. This is often unexpected, especially when paginating results or showing the “most recent” items at the top.

Example

-- NULLs appear first (default behavior)
SELECT * FROM orders ORDER BY shipped_at DESC LIMIT 10;

-- Unshipped orders (shipped_at IS NULL) come before shipped ones

Why it happens

PostgreSQL treats NULL as larger than any non-null value. Combined with the sort direction, this produces:

Direction	Default NULL placement
ASC	NULLs last
DESC	NULLs first

With DESC, the largest values come first — and since NULL is considered larger than everything, it sorts to the top. Most callers don’t want NULL rows at the top of a descending result set.

How to fix it

Add NULLS LAST to push NULLs to the end:

-- NULLs now appear at the end
SELECT * FROM orders ORDER BY shipped_at DESC NULLS LAST LIMIT 10;

Or filter NULLs out entirely when they aren’t relevant:

-- Only shipped orders
SELECT * FROM orders
WHERE shipped_at IS NOT NULL
ORDER BY shipped_at DESC
LIMIT 10;

Detection

Query Doctor flags this nudge when it finds an ORDER BY column with DESC direction and no explicit NULLS LAST or NULLS FIRST modifier, provided the WHERE clause does not already include an IS NOT NULL filter on the same column. The nudge marker points to the column in the ORDER BY clause.