Top 3 Products When Some Prices Are NULL

SQLSortingJuniorMidUpdated Nov 10, 2026

Return the 3 most expensive products. Unpriced products (NULL price) should not appear.

Schema

CREATE TABLE products (
  id     BIGINT PRIMARY KEY,
  name   TEXT NOT NULL,
  price  NUMERIC  -- NULL until the product is priced
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT name, price
FROM products
WHERE price IS NOT NULL
ORDER BY price DESC
LIMIT 3;

Postgres sorts NULL as larger than any value, so ORDER BY price DESC puts the unpriced products first and (a) can return three NULLs. Filtering them out with WHERE price IS NOT NULL (b) fixes that. (c) returns the cheapest products. (d) only returns products tied for the single highest price, which is usually one row.

Going Deeper

NULLS FIRST and NULLS LAST override the default for one sort key: ORDER BY price DESC NULLS LAST keeps unpriced rows but moves them to the end. Defaults differ between databases (MySQL and SQL Server sort NULL as the smallest value), so the same ORDER BY can surface NULLs in one engine and hide them in another.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Option (c)

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 3;

Option (d)

SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products)
LIMIT 3;