Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

SELECT DISTINCT fails because ORDER BY uses a column that is not selected

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

Short answer

With DISTINCT, PostgreSQL cannot sort by a value that is not in the output, because the sort value may differ inside one group of duplicates. Add the ORDER BY expression to the select list, group with an aggregate (GROUP BY and max), or switch to DISTINCT ON when you want one row per key.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Compare the ORDER BY expression with the select list, character for character. A function call in ORDER BY must also appear in the list in the same form.

    -- fails: created_at is sorted but not selected
    SELECT DISTINCT user_id FROM events ORDER BY created_at DESC;
  2. 2Match what you see, then jump to its fix

    1. 1The first message above, and the ORDER BY column is missing from the select list.The sort column is not part of the outputFix
    2. 2Adding the column gives several rows for the same user.You want one row per key, sorted by a value inside each groupFix
    3. 3You need other columns of the newest row, not only the key.You want the whole latest row per keyFix

What does the error look like?

  • ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  • SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Cause 1 of 3

The sort column is not part of the output

1Check

The server code explains the rule: a sort item that is not in the output would sort by a value that is not necessarily unique within one DISTINCT group. So the query is refused.

SELECT DISTINCT user_id, created_at FROM events ORDER BY created_at DESC;

2Fix

If you really need one row per distinct pair, put the column in the select list. If you want one row per user, use one of the next two causes instead.

SELECT DISTINCT user_id, created_at
FROM events
ORDER BY created_at DESC;

3Verify

The query runs, and the output has the extra column.

EXPLAIN SELECT DISTINCT user_id, created_at FROM events ORDER BY created_at DESC;

Confirmed by raw.githubusercontent.com

Did this fix it?

Cause 2 of 3

You want one row per key, sorted by a value inside each group

1Check

DISTINCT works on the whole output row, so extra columns make more distinct rows. An aggregate per group gives one value per key that you can sort by.

SELECT user_id, count(*) FROM events GROUP BY user_id ORDER BY count(*) DESC LIMIT 5;

2Fix

Group by the key and sort by an aggregate of the hidden column. Expressions in ORDER BY may use aggregates when the query groups.

SELECT user_id
FROM events
GROUP BY user_id
ORDER BY max(created_at) DESC;

3Verify

You get one row per user in latest-first order.

SELECT user_id, max(created_at) FROM events GROUP BY user_id ORDER BY 2 DESC LIMIT 5;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

You want the whole latest row per key

1Check

DISTINCT ON keeps the first row of each group, and the first row is unpredictable unless ORDER BY decides it. The DISTINCT ON expressions must match the leftmost ORDER BY expressions.

SELECT DISTINCT ON (user_id) user_id, created_at, kind FROM events ORDER BY user_id, created_at DESC;

2Fix

Start ORDER BY with the DISTINCT ON key, then the column that picks the row. If you need the final result in another order, wrap it in a subquery.

SELECT *
FROM (
  SELECT DISTINCT ON (user_id) user_id, created_at, kind
  FROM events
  ORDER BY user_id, created_at DESC
) latest
ORDER BY created_at DESC;

3Verify

One row per user, each with that user's newest created_at.

SELECT user_id, count(*) FROM (SELECT DISTINCT ON (user_id) user_id FROM events ORDER BY user_id, created_at DESC) t GROUP BY user_id HAVING count(*) > 1;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Decide first whether you want one row per key or per whole row, then pick DISTINCT, GROUP BY or DISTINCT ON.
  • In ORMs, log the generated SQL when a scope uses distinct together with order.
  • Never use DISTINCT only to hide duplicates from a join; fix the join.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.