How to resolve "ORDER BY clause is not in SELECT list" caused MySQL 5.7 with SELECT DISTINCT and ORDER BY

How to resolve "ORDER BY clause is not in SELECT list" caused MySQL 5.7 with SELECT DISTINCT and ORDER BY

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

MySQL 5.7 Enforces ORDER BY on SELECT DISTINCT Lists

If you are migrating legacy codebases to Ubuntu with MySQL 5.7, you will almost certainly encounter a specific runtime error that breaks your application's logic: Expression #2 of ORDER BY clause is not in SELECT list... this is incompatible with DISTINCT. This isn't just a syntax error; it is a strict enforcement of standard SQL semantics that older versions of MySQL often ignored.

The core issue arises when attempting to combine SELECT DISTINCT with an ORDER BY clause that references columns not included in the projection. In your specific case, you are trying to deduplicate results while ordering by a column (p.products_date_added) that is absent from the final result set. The error message explicitly states: "Expression #2 of ORDER BY clause is not in SELECT list."

Why MySQL 5.7 Rejects This Query

Prior to version 8, MySQL was lenient with SQL standards. It allowed queries where the ORDER BY column did not exist in the SELECT list, assuming that if you wanted to sort by a hidden column, you would still retrieve it for the sort operation and then discard it. However, this behavior conflicts with how DISTINCT works logically.

When you use DISTINCT, the database engine must evaluate every row as a unique set of selected columns. If your query asks MySQL to order rows by products_date_added but only returns products_id and products_price, the engine faces a logical paradox. To ensure a deterministic sort order, it needs the value of the sort key for every row. While the database could theoretically fetch that hidden column just for sorting (an "invisible" projection), MySQL 5.7's optimizer refuses to execute DISTINCT with such an ambiguous sort key combination to prevent unpredictable results.

The Incorrect Workaround

You mentioned a workaround where you order only by RAND(). This works because RAND() generates a unique value for every single row in the result set before distinctness is evaluated or applied. Since the sort key exists conceptually for every row, the query remains valid. However, relying on RAND() to deduplicate data is functionally unsound. It does not guarantee that you are getting one unique record per logical entity; it merely sorts rows randomly. If you have duplicate entries based on your other columns, DISTINCT will eventually filter them out, but the random sort order combined with LIMIT can lead to inconsistent pagination and data loss across different requests.

The Correct Resolution: Add the Column to SELECT

The only robust solution that satisfies MySQL 5.7's strict requirements is to include the column used for sorting in your SELECT list. You must modify your query to project p.products_date_added alongside your other columns. Once this column is present in the result set, you can use it for sorting without violating the rules of DISTINCT.

Here is how the corrected PHP PDO statement should look:

$Qproduct = $OSCOM_PDO->prepare('SELECT DISTINCT p.products_id, 
            p.products_price, 
            p.products_date_added
            FROM :table_products p 
            LEFT JOIN :table_specials s ON p.products_id = s.products_id 
            WHERE products_status = :products_status
            AND products_view = :products_view
            AND p.products_archive = :products_archive 
            ORDER BY p.products_date_added DESC, RAND()
            LIMIT :products_limit');

$Qproduct->bindInt(':products_status', 1);
$Qproduct->bindInt(':products_view', 1);
$Qproduct->bindInt(':products_archive', 0);
$Qproduct->bindInt(':products_limit', (int)MODULE_FRONT_PAGE_NEW_PRODUCTS_MAX_DISPLAY);

By adding p.products_date_added to the selection, the database no longer needs to guess which column you are referencing. It can now apply the standard sorting logic against the projected columns. Note that we also added RAND() as a secondary sort key if you intended to break ties arbitrarily, though strictly ordering by date descending is often sufficient for "new products" queries.

Trade-offs and Performance Implications

Adding a column to your SELECT list does increase the memory footprint of the result set slightly, as MySQL must now hold that extra data in memory while evaluating distinctness. However, this is negligible compared to the performance hit caused by a query that fails entirely or requires complex subqueries to work around the limitation.

If you are working with a massive dataset, ensure that an index exists on products_date_added if you frequently sort by it. Without an index, the database must perform a full table scan and sort operation for every request, which can degrade performance significantly as data grows.

This error is a common stumbling block when modernizing older codebases written for MySQL 5.6 or earlier. It forces developers to align their SQL syntax with standard-compliant behavior, ensuring that queries remain portable across different database engines and versions.

Our network