📋

Description

This error occurs when your `ORDER BY` clause references a column or expression that is not explicitly included in the `SELECT` list, and the `ONLY_FULL_GROUP_BY` SQL mode is enabled. MySQL, in this strict mode, requires that any non-aggregated column used for ordering must also be present in the query's output.

💬

Error Message

Expression #%u of ORDER BY clause is not in SELECT list, references column '%s' which is not in SELECT list; this is incompatible with %s

🔍

Known Causes

4 known causes

⚠️

Strict SQL Mode Active

The `ONLY_FULL_GROUP_BY` SQL mode is enabled, enforcing a rule that all non-aggregated columns in `ORDER BY` must also appear in the `SELECT` list.

⚠️

Unselected Column in ORDER BY

You are attempting to sort the query results by a column that is not explicitly listed in the `SELECT` clause of your query.

⚠️

Complex Expression Not in SELECT

An `ORDER BY` clause uses a complex expression or function that is not duplicated or aliased within the `SELECT` list.

⚠️

Incompatible GROUP BY Interaction

When `GROUP BY` is used, the `ORDER BY` clause references a column that is neither in `SELECT`, an aggregate, nor functionally dependent on `GROUP BY` columns, clashing with strict SQL modes.

🛠️

Solutions

4 solutions available

1. Include ORDER BY Columns in SELECT easy

The most direct solution is to add the column(s) used in the ORDER BY clause to your SELECT list.

1

Examine your `SELECT` statement and identify the columns or expressions used in the `ORDER BY` clause.

2

Modify your `SELECT` statement to include these identified columns or expressions. If you only need them for ordering and not for output, you can still select them.

SELECT column1, column2, column_in_order_by
FROM your_table
ORDER BY column_in_order_by;

3

If you are using an alias in your `ORDER BY` clause, ensure that alias is also defined in your `SELECT` list.

SELECT column1 AS alias_for_order_column
FROM your_table
ORDER BY alias_for_order_column;

2. Use Column Indexes in ORDER BY easy

Alternatively, you can refer to the columns in the ORDER BY clause by their ordinal position (index) in the SELECT list.

1

Identify the position (starting from 1) of the column(s) in your `SELECT` list that you intend to sort by.

2

Modify your `ORDER BY` clause to use the corresponding numerical index instead of the column name.

SELECT column1, column2, column3
FROM your_table
ORDER BY 3; -- Sorts by column3

3

For multiple columns, use their respective indices separated by commas.

SELECT column1, column2, column3
FROM your_table
ORDER BY 3 DESC, 2 ASC; -- Sorts by column3 DESC, then by column2 ASC

3. Remove ORDER BY Clause if Not Needed easy

If the ordering is not critical for your query's purpose, simply remove the ORDER BY clause.

1

Review the requirements for your query. Determine if the specific order of results is essential.

2

If the order is not required, delete the `ORDER BY` clause from your SQL statement.

SELECT column1, column2
FROM your_table;

4. Refactor Subqueries or Derived Tables medium

If the error occurs within a subquery or derived table, ensure the outer query's SELECT list includes the columns used for ordering within the inner query.

1

Locate the subquery or derived table that is causing the error. Examine its `SELECT` and `ORDER BY` clauses.

2

Ensure that any columns used in the `ORDER BY` clause of the subquery/derived table are also present in the `SELECT` list of that same subquery/derived table.

-- Incorrect example within a derived table:
SELECT *
FROM (
    SELECT column1, column2
    FROM another_table
    ORDER BY column2 -- Error here if column2 is not in SELECT
) AS sub_query;

3

Correct the subquery/derived table's `SELECT` list to include the ordering column(s).

-- Corrected example within a derived table:
SELECT *
FROM (
    SELECT column1, column2
    FROM another_table
    ORDER BY column2
) AS sub_query;

4

If the outer query is ordering by a column from the derived table, ensure that column is also selected by the derived table.

-- Example where outer query needs column from derived table:
SELECT dt.column2
FROM (
    SELECT column1, column2
    FROM another_table
    ORDER BY column2
) AS dt
ORDER BY dt.column2; -- This is valid as dt.column2 is selected by the derived table.

🔗

Related Errors

5 related errors

MySQL Error 3065: ORDER BY Not in SELECT