MySQL Error 1055: Fix ONLY_FULL_GROUP

MySQL Error 1055 (SQLSTATE 42000) means a grouped query’s SELECT list, HAVING, or ORDER BY refers to a nonaggregated value that MySQL cannot determine for each group while ONLY_FULL_GROUP_BY is enabled. The right fix depends on the result you need; adding every selected column to GROUP BY can change what each row represents.

Check the session SQL mode

SELECT @@SESSION.sql_mode;

MySQL 8.4 enables ONLY_FULL_GROUP_BY by default, but the session mode can be changed by server or connection settings. Check the mode of the connection that runs the failing query. See MySQL’s GROUP BY rules.

Why MySQL raises Error 1055

Assume an employees table has multiple employees in a department:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  department_id INT NOT NULL,
  employee_name VARCHAR(100) NOT NULL,
  salary DECIMAL(10, 2) NOT NULL
);

This query asks for one employee_name per department, but a department can contain many names:

SELECT department_id, employee_name, MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

MAX(salary) identifies a maximum value; it does not tell MySQL which employee_name to return. MySQL rejects this ambiguous result with Error 1055 instead of choosing an arbitrary name.

Choose a fix that matches the result

Return only the grouped summary

If the report needs only each department’s highest salary, remove the employee name:

SELECT department_id, MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

Select a column determined by the group key

MySQL accepts a nonaggregated column when it can prove that the grouping columns uniquely determine it. For example, if departments.department_id is the table’s primary key, department_name depends on that key:

SELECT
  d.department_id,
  d.department_name,
  COUNT(e.employee_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
  ON e.department_id = d.department_id
GROUP BY d.department_id;

Keep the primary or unique NOT NULL constraint that establishes this relationship; do not rely only on the current data happening to contain one name per key.

Return one employee per department

To return the employee row with the highest salary, use a window function in MySQL 8.0 or later. The unique employee_id is a deterministic tie-breaker:

WITH ranked_employees AS (
  SELECT
    employee_id,
    department_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC, employee_id
    ) AS row_num
  FROM employees
)
SELECT department_id, employee_id, employee_name, salary
FROM ranked_employees
WHERE row_num = 1
ORDER BY department_id;

If all employees tied for the top salary should be returned, use RANK() ordered by salary DESC and filter for rank 1; leave employee_id out of that ranking order so equal salaries remain tied. For more per-group ranking patterns, see Top N rows per group in MySQL.

Use `ANY_VALUE()` only when any value is acceptable

ANY_VALUE(employee_name) tells MySQL not to check whether the name is deterministic. It does not select the employee whose salary equals MAX(salary), and ANY_VALUE() is not an aggregate function. Use it only when any name in the group is acceptable. See the MySQL ANY_VALUE() reference and the official function documentation.

Avoid disabling `ONLY_FULL_GROUP_BY` as a general fix

Disabling the mode can make an ambiguous query run, but MySQL is then free to choose any nonaggregated value from each group. Adding ORDER BY does not control which value is chosen because sorting happens after the grouped values are selected. Prefer a grouped summary, a proven functional dependency, or an explicit row-ranking query. See the MySQL GROUP BY tutorial.