How to Fix MySQL Error 3065: Expression in ORDER BY Clause Is Not in SELECT List

ERROR 3065 (HY000): Expression #N of ORDER BY clause is not in SELECT list, references column '<table>.<column>' which is not in SELECT list; this is incompatible with DISTINCT is raised when a query uses DISTINCT (or certain aggregation contexts) and the ORDER BY clause references a column or expression that is not present in the SELECT list. The error symbol is ER_ORDER_WITH_PROC.

Impact

The query is rejected outright — no rows are returned and execution stops immediately. This commonly surfaces when DISTINCT is combined with an ORDER BY on a column that was intentionally excluded from the select list, or when a query is migrated from a permissive MySQL 5.x environment to a stricter MySQL 5.7+ or 8.x environment where ONLY_FULL_GROUP_BY is enabled by default.

Applications using ORMs such as Hibernate, ActiveRecord, or SQLAlchemy may encounter this error at runtime when the ORM generates a DISTINCT query with an ORDER BY on a joined column — a pattern common in has_many associations or eager loading with sorting.

Common Causes

DISTINCT with ORDER BY on an excluded column. The SQL standard requires that when DISTINCT is used, every column in ORDER BY must also appear in the SELECT list, because the sort must be applied to the deduplicated result set.
```
-- Fails: last_login is not in the SELECT list
SELECT DISTINCT user_id, email
FROM users
ORDER BY last_login DESC;
```
GROUP BY query under ONLY_FULL_GROUP_BY with a non-aggregated ORDER BY expression. In MySQL 5.7+ the ONLY_FULL_GROUP_BY SQL mode is enabled by default. A GROUP BY query whose ORDER BY references a non-aggregated column not functionally dependent on the GROUP BY key triggers a related rejection.
```
-- Fails under ONLY_FULL_GROUP_BY: last_login is neither aggregated nor in GROUP BY
SELECT department_id, COUNT(*) AS cnt
FROM employees
GROUP BY department_id
ORDER BY last_login;
```
Window functions with a derived column not aliased in SELECT. Queries that use OVER() clauses and then attempt to order by the underlying partition column rather than the window function result can hit this restriction in certain MySQL 8 contexts.
ORM-generated queries with implicit sorting on a join column. ActiveRecord's order(), Django's order_by(), and similar ORM methods append raw column references to ORDER BY. When the ORM also adds DISTINCT (e.g., to prevent duplicate rows from a join), the generated SQL frequently violates this constraint.

Troubleshooting and Resolution Steps

Identify the offending column. The error message includes the expression number and the fully-qualified column name. Use that to locate the column in your ORDER BY clause.

Add the column to the SELECT list. The most straightforward fix is to include the ORDER BY column in the projection:

-- Fixed: last_login is now in the SELECT list
SELECT DISTINCT user_id, email, last_login
FROM users
ORDER BY last_login DESC;

Remove DISTINCT if it is not required. If deduplication is not needed, drop DISTINCT and the restriction no longer applies:
```
SELECT user_id, email
FROM users
ORDER BY last_login DESC;
```
Replace DISTINCT with GROUP BY and aggregate the sort column. When you need deduplication but cannot add the column to the select list, restructure using GROUP BY:
```
SELECT user_id, email, MAX(last_login) AS last_login
FROM users
GROUP BY user_id, email
ORDER BY last_login DESC;
```
For ORM-generated queries, use a subquery or CTE. Wrap the sorted query in a subquery that exposes the sort column, then project only the columns you need from the outer query:
```
SELECT user_id, email
FROM (
    SELECT user_id, email, last_login
    FROM users
    ORDER BY last_login DESC
) AS sorted_users;
```
Check the active SQL mode if you have recently migrated from MySQL 5.6 or are running on a shared host:
```
SELECT @@sql_mode;
```
If ONLY_FULL_GROUP_BY is present and you need to relax it for a legacy application, you can remove it for the session (prefer fixing the query instead):
```
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY,', ''));
```

Additional Information

Related error codes: Error 1055 (ER_WRONG_FIELD_WITH_GROUP) is the companion error for GROUP BY violations under ONLY_FULL_GROUP_BY; Error 3065 specifically targets the DISTINCT + ORDER BY mismatch and certain procedure contexts.
MySQL version differences: MySQL 5.6 and earlier silently accepted DISTINCT queries with non-selected ORDER BY columns, producing indeterminate sort behaviour. MySQL 5.7 introduced stricter enforcement. MySQL 8.0 continues this behaviour.
ONLY_FULL_GROUP_BY and error 3065: Although error 3065 is most commonly encountered with DISTINCT, ONLY_FULL_GROUP_BY tightens related constraints on GROUP BY queries and may cause similar rejections surfaced under the same error code in some MySQL 8 builds.
ORM workarounds: In ActiveRecord (Rails), use unscope(:order) before applying distinct or add the sort column via select("..., last_login"). In Django, ensure values() includes every field referenced by order_by() when distinct() is also applied.

Frequently Asked Questions

Why did this query work in MySQL 5.6 but fail in MySQL 5.7+? MySQL 5.6 allowed ORDER BY on columns not in the SELECT list even with DISTINCT, simply ignoring the ambiguity. MySQL 5.7 enforced the SQL standard more strictly and added ONLY_FULL_GROUP_BY to the default SQL mode, causing previously accepted queries to be rejected. Fixing the query rather than downgrading the SQL mode is strongly recommended.

Can I just disable the SQL mode to get rid of this error? Removing ONLY_FULL_GROUP_BY (or other strict modes) from @@sql_mode will suppress some related errors, but error 3065 for DISTINCT + non-selected ORDER BY is enforced regardless of SQL mode in MySQL 5.7+. The correct fix is to align the SELECT list with the ORDER BY clause.

Does this error affect INSERT ... SELECT or CREATE TABLE ... SELECT statements? Yes. If the inner SELECT uses DISTINCT and the ORDER BY references a column not in the select list, MySQL will raise error 3065 during execution of the INSERT or CREATE TABLE statement.

My ORM generates the query automatically — how do I fix it without writing raw SQL? In most ORMs you can either: (a) add the sort column to the select projection explicitly, (b) use a subquery/CTE to isolate the sort from the outer distinct projection, or (c) post-process in application code if the result set is small enough. Consult your ORM documentation for select_extra, annotate, or raw query escape hatches.