What happens when ORDER BY and GROUP BY use different columns — how does MySQL handle sorting and aggregation? | Clauses | mysql Interview Questions

Q: What happens when ORDER BY and GROUP BY use different columns — how does MySQL handle sorting and aggregation?

In MySQL, the **GROUP BY clause** groups rows based on one or more columns, while the **ORDER BY clause** sorts the final result set. When these clauses reference different columns, MySQL first performs aggregation according to GROUP BY, then sorts the aggregated results based on ORDER BY. GROUP BY determines how rows are combined into summary rows, using aggregate functions if specified. ORDER BY is applied **after aggregation**, and can sort by any column or expression in the SELECT list, including aggregate results or non-grouped columns (depending on SQL mode). When ORDER BY references a column not in GROUP BY, MySQL uses the value from an arbitrary row within each group if the ONLY_FULL_GROUP_BY mode is disabled. If ONLY_FULL_GROUP_BY is enabled, the query must use columns in GROUP BY or aggregates. This separation allows grouping and sorting to be independent, giving flexibility in presenting aggregated data. In short, GROUP BY defines aggregation boundaries, while ORDER BY defines the presentation order of the aggregated rows, even if they use different columns. SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ORDER BY avg_salary DESC; -- Aggregates employees per department but sorts the result by average salary instead of department_id.

Difficulty: 5/10

GROUP BY, ORDER BY, query execution

Handling Different Columns in GROUP BY and ORDER BY in MySQL

In MySQL, the GROUP BY clause groups rows based on one or more columns, while the ORDER BY clause sorts the final result set. When these clauses reference different columns, MySQL first performs aggregation according to GROUP BY, then sorts the aggregated results based on ORDER BY.

Key Points About GROUP BY vs ORDER BY with Different Columns

1
GROUP BY determines how rows are combined into summary rows, using aggregate functions if specified.
2
ORDER BY is applied after aggregation, and can sort by any column or expression in the SELECT list, including aggregate results or non-grouped columns (depending on SQL mode).
3
When ORDER BY references a column not in GROUP BY, MySQL uses the value from an arbitrary row within each group if the ONLY_FULL_GROUP_BY mode is disabled. If ONLY_FULL_GROUP_BY is enabled, the query must use columns in GROUP BY or aggregates.
4
This separation allows grouping and sorting to be independent, giving flexibility in presenting aggregated data.

In short, GROUP BY defines aggregation boundaries, while ORDER BY defines the presentation order of the aggregated rows, even if they use different columns.

Example: GROUP BY and ORDER BY on Different Columns

Scenario Questions

0-2 years experience

2-5 years experience

What happens when ORDER BY and GROUP BY use different columns — how does MySQL handle sorting and aggregation?

Handling Different Columns in GROUP BY and ORDER BY in MySQL

Scenario Questions

Follow-up Questions