SELECT DISTINCT fails because ORDER BY uses a column that is not selected
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select listShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The first message above, and the ORDER BY column is missing from the select list.The sort column is not part of the outputFix - 2
Adding the column gives several rows for the same user.You want one row per key, sorted by a value inside each groupFix - 3
You need other columns of the newest row, not only the key.You want the whole latest row per keyFix
- 1
What does the error look like?
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select listSELECT 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
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix