Fix MySQL Error 3065: ORDER BY clause is not in SELECT list with DISTINCT

The ProblemImagine you need a unique list of category IDs from a 50,000-row product table, sorted so the most recently updated categories appear first. You might try a query like this:

SELECT DISTINCT category_id 
FROM products 
ORDER BY updated_at DESC;

MySQL blocks this request immediately. Instead of your data, you get a specific error message:

ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'products.updated_at' which is not in SELECT list; this is incompatible with DISTINCT

Why This HappensMySQL follows strict set logic when processing DISTINCT. This keyword instructs the engine to merge duplicate rows into a single representative row.

The conflict happens because of ambiguity. If you have three rows with category_id = 5 but three different updated_at timestamps, the database doesn't know which timestamp to use for the final sort. Sorting by a hidden value that isn't in your output is logically impossible once duplicates are collapsed.

How to Fix It### Approach 1: Adding the Sorting Column to the SELECT ListThe fastest way to clear the error is to include the column you're sorting by in the SELECT statement. This removes the ambiguity by making the sort value part of the unique set.

SELECT DISTINCT category_id, updated_at 
FROM products 
ORDER BY updated_at DESC;

The Catch: This often changes your results. If one category_id has five different updated_at values, you will now see five rows for that category instead of one. Use this only if you actually need to see the timestamps.

Approach 2: Using GROUP BY (The Preferred Method)I recommend GROUP BY for 90% of these cases. It is more flexible because you can use aggregate functions like MAX() to tell MySQL exactly which value should determine the sort order.

SELECT category_id 
FROM products 
GROUP BY category_id 
ORDER BY MAX(updated_at) DESC;

This query tells the database: "Group everything by category, pick the latest update time from each group, and sort the list by those timestamps." It’s precise and returns exactly one row per category.

Approach 3: Using a SubqueryFor complex reports where your main SELECT must stay clean, handle the heavy lifting in a subquery first.

SELECT category_id FROM (
    SELECT category_id, MAX(updated_at) as latest_update
    FROM products
    GROUP BY category_id
) AS sorted_data
ORDER BY latest_update DESC;

Prevention and Best PracticesThis error became standard after MySQL 5.7 introduced stricter sql_mode settings by default. To stay ahead of it:

• Prioritize GROUP BY: If your sorting logic relies on data not being displayed, GROUP BY is almost always the better tool.- Respect ONLY_FULL_GROUP_BY: You might find advice online telling you to disable this mode. Don't do it. Strict mode prevents subtle data bugs that could lead to incorrect report values.- Set Logic: Always remember that DISTINCT operates on the entire returned row. If a column is invisible to the user, it is invisible to the sort engine.## VerificationAfter applying a fix, check the performance with EXPLAIN. You want to avoid "filesorts" on large datasets.

EXPLAIN SELECT category_id 
FROM products 
GROUP BY category_id 
ORDER BY MAX(updated_at) DESC;

If the Extra column shows Using filesort on a table with 100,000+ rows, consider adding a composite index on (category_id, updated_at). This allows MySQL to read the sorted data directly from the index, often reducing query time from seconds to milliseconds.