✏️ Explanatory Question
Level: Hard — Tests whether you understand aggregation grain and why MySQL changed its default behaviour.
employees table| emp_id | name | dept_id | salary |
|---|---|---|---|
| 1 | Rumman | 10 | 90000 |
| 2 | Krushna | 10 | 60000 |
| 3 | Swetha | 10 | 75000 |
| 4 | Ritesh | 20 | 50000 |
| 5 | Manjula | 20 | 80000 |
The cause: When you GROUP BY dept_id, each group collapses many rows into one. But if you also SELECT name (a non-aggregated column), MySQL has 3 names for dept 10 and must pick just one. Older MySQL silently returned an arbitrary/indeterminate value; MySQL 8.0 enables ONLY_FULL_GROUP_BY by default and rejects the ambiguous query.
SELECT dept_id, name, MAX(salary) AS top_salary
FROM employees
GROUP BY dept_id;| MySQL Version | Result |
|---|---|
| 5.6 / 5.7 (default OFF) | Runs, but wrong: returns a random name (e.g., 'Rumman' or 'Krushna') NOT necessarily the top earner |
| 8.0 (ONLY_FULL_GROUP_BY ON) | ERROR 1055 — nonaggregated column 'name' not in GROUP BY |
dept 10 → 'Krushna' → 90000 — pairing Krushna's name with Rumman's salary. The name and the MAX come from different rows! This silently corrupts reports.
-- WRONG: name is ambiguous, may not match the MAX salary
SELECT dept_id, name, MAX(salary)
FROM employees GROUP BY dept_id;
-- CORRECT (MySQL 8.0+): window function ranks within each dept
SELECT dept_id, name, salary
FROM (
SELECT dept_id, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn = 1;
-- CORRECT (any version): correlated subquery matching the real top row
SELECT e.dept_id, e.name, e.salary
FROM employees e
WHERE e.salary = (
SELECT MAX(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
);| dept_id | name | salary |
|---|---|---|
| 10 | Rumman | 90000 |
| 20 | Manjula | 80000 |
Now the name correctly matches the top salary in each department.
GROUP BY the primary key (e.g., GROUP BY emp_id), MySQL 8.0 allows selecting other columns, because they are functionally dependent on the PK and therefore unambiguous.
ONLY_FULL_GROUP_BY from sql_mode, but the correct answer is: don't — fix the queries instead, because the mode is protecting you from silently wrong results.