invalid_column_reference for SELECT DISTINCT, ORDER BY expressions must appear in select list — 42P10

Symptoms

A SELECT DISTINCT query ordered by an expression that is not present in the select list. With DISTINCT, ORDER BY expressions must appear in the output, so PostgreSQL raises SQLSTATE 42P10 (invalid_column_reference).

• ORDER BY references a column not selected.
• Only an issue with DISTINCT.
• Common when sorting by a column you didn’t output.

What the server log shows

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

Why PostgreSQL raises this — what the manual says

As the SELECT reference (DISTINCT Clause / ORDER BY Clause) explains:

With SELECT DISTINCT, duplicate output rows are removed before ORDER BY is applied, so every ORDER BY expression must also appear in the select list; otherwise PostgreSQL cannot determine the ordering of the deduplicated rows.

DISTINCT collapses duplicate output rows; sorting by a value that isn’t in the output would be ambiguous after deduplication. PostgreSQL therefore requires ORDER BY expressions to be in the select list, else 42P10.

Common causes

• Ordering by a column omitted from a DISTINCT select list.
• Adding DISTINCT to a query that orders by a non-selected column.
• Expecting ORDER BY to use input columns with DISTINCT.

How to fix it

1. Add the ORDER BY expression to the select list.
2. Use DISTINCT ON (…) if you need ordering by a leading key.
3. Remove DISTINCT if it isn’t needed, or deduplicate differently.

Related & next steps

• SQLSTATE 42P10 — Invalid Column Reference (full reference)
• Message: ORDER BY position is not in select list
• Glossary: DISTINCT

Reference: PostgreSQL 18 — SELECT (DISTINCT).