GROUP BY vs. ORDER BY: Grouping Is Not Sorting

The original version of this lesson treated GROUP BY as a kind of sorting routine, comparing it directly to alphabetizing a list of names. That framing doesn't hold up: GROUP BY groups rows together for aggregation, but it does not guarantee anything about the order those groups appear in. Sorting is ORDER BY's job, not GROUP BY's. This isn't a matter of opinion or a recent change; it's been true throughout the SQL standard, and it's still true in Oracle AI Database 26ai. This lesson corrects that conflation and lays out what each clause is actually responsible for.

This mix-up isn't a minor academic distinction either. It's the kind of misconception that survives in production code for years, because a query written against a small test table often does come back looking sorted, purely by coincidence of how the optimizer chose to execute it that day. The query keeps "working" right up until the table grows, an index changes, or the optimizer picks a different execution plan, at which point the output order shifts and nobody can explain why, because nothing in the query ever actually asked for a specific order.

GROUP BY answers the question "which rows belong together?" ORDER BY answers a completely different question: "in what sequence should the result set be presented?" A query can group without sorting, sort without grouping, or do both together, but one doesn't imply the other.

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

Run this exact query twice against an unchanged table and you're not guaranteed to see the departments come back in the same order both times. The database is free to group however is most efficient internally, whether that's a hash-based grouping, a sort-based grouping, or something else entirely, and none of those internal choices are contractually promised to you as output order. If the order happens to look sorted, that's incidental, not guaranteed, and code that silently relies on it is one execution-plan change away from breaking.

Oracle specifically exposes this choice in its execution plans as HASH GROUP BY and SORT GROUP BY, two different physical operations the optimizer can pick to implement the exact same logical GROUP BY clause. A HASH GROUP BY builds an in-memory hash table keyed on the grouping columns and produces groups in whatever order they happen to fall out of that hash table, which has nothing to do with alphabetical or numeric order. A SORT GROUP BY sorts the rows by the grouping columns first and then collapses adjacent matching rows, which happens to produce output that looks sorted purely as a side effect of the mechanism it used internally. The optimizer picks between the two based on cost estimates, available indexes, and memory, and it can switch its choice for the same query from one execution to the next as statistics change. A query that "always" looked sorted because Oracle happened to favor SORT GROUP BY for it can start returning unordered output the day the optimizer decides HASH GROUP BY is cheaper, with no change to the SQL text at all.

This has real precedent beyond Oracle too. Some older database engines, older versions of MySQL among them, used to sort rows as a side effect of how GROUP BY was executed internally, which reinforced exactly this misconception for a generation of SQL writers. That behavior was later removed specifically to bring those engines in line with the standard, and any query that had come to depend on it broke the moment the change shipped. The lesson generalizes well beyond that one engine: never treat an incidental result of how a query happens to execute as a guarantee about what it will do next time.

To guarantee an order, add ORDER BY explicitly:

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
ORDER BY department_id;

It's an understandable mix-up, because both clauses do involve comparing column values to decide what goes where. When you sort a list of names alphabetically, you compare the first letter, then the second, then the third, only moving to the next letter when the current one ties:

James
Jinkies
Jobs

ORDER BY does exactly this when you give it multiple sort columns: it uses the first column as the primary key, then breaks ties with the second, and so on.

GROUP BY does something related but distinct. When you group by more than one column, it doesn't compare column-by-column to break ties; it checks whether every grouping column's value matches exactly across two rows before deciding they belong in the same bucket. Two rows with the same department_id but a different job_id land in different groups entirely, not in the same group with a tiebreak applied. Grouping is about bucket membership; sorting is about sequence. The letter-by-letter comparison habit that makes sense for ORDER BY doesn't map cleanly onto what GROUP BY is doing internally, even though both involve looking at columns left to right.

One more source of the confusion worth naming directly: linguistic collation. You can apply a specific collation, such as COLLATE or an NLSSORT-based comparison, to the expressions in a GROUP BY clause, and doing so changes which rows the database considers equal for grouping purposes. Two strings that differ only in accent marks or case might be treated as the same group under one collation and as separate groups under another. That's a real and useful feature, but it only affects group membership, the same "which rows belong together" question GROUP BY always answers. It has no bearing on what order the resulting groups are presented in; that's still entirely ORDER BY's responsibility.

One rule from the original lesson does hold up, just not for the reason given. This query is invalid:

SELECT *
FROM authors
GROUP BY au_lname;

The problem isn't a missing "sort instruction." It's that * pulls in every column from authors, but only au_lname is listed in the GROUP BY clause. Every non-aggregated column in the SELECT list has to also appear in GROUP BY, or be wrapped in an aggregate function, or the database has no way to know which value to display for a column that isn't part of the grouping. Oracle raises ORA-00979: not a GROUP BY expression for exactly this reason.

Two corrected versions fix the same underlying problem in different ways, depending on what you actually need out of the query. If you want every column shown and grouping was never really the goal, drop GROUP BY entirely and use DISTINCT instead. If you genuinely want a per-author-last-name summary, name only the columns you intend to aggregate:

SELECT au_lname, COUNT(*) AS author_count
FROM authors
GROUP BY au_lname;

GROUP BY's real purpose is partitioning rows into non-overlapping subsets so an aggregate function can summarize each subset independently. Suppose you want, for each department, the department number, the number of employees in it, and their average salary:

SELECT dno, COUNT(*), AVG(salary)
FROM employee
GROUP BY dno;

Here, the employee rows are partitioned by dno, so each group holds only the employees who work in that department. COUNT and AVG then run once per group rather than once per row. Notice the SELECT list contains only the grouping column and aggregate functions, which is the pattern every valid GROUP BY query follows.

Extending the same query with a second department-level metric shows how naturally multiple aggregates fit into a single grouped query:

SELECT dno, COUNT(*) AS headcount, AVG(salary) AS avg_salary, MAX(salary) AS top_salary
FROM employee
GROUP BY dno;

Every one of those aggregate functions runs against the same set of groups established by the single GROUP BY dno clause. Adding a fourth summary column doesn't require a fourth pass over the table or a second GROUP BY; it's computed alongside the others in the same grouping operation.

Part of why GROUP BY can't double as a sorting mechanism becomes clearer once you know the order a query is logically processed in, which is roughly: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, and only then ORDER BY, last of all.

That ordering explains a couple of things that otherwise seem arbitrary. ORDER BY is allowed to reference a column alias defined in the SELECT list, because by the time ORDER BY runs, SELECT has already been evaluated and that alias exists. GROUP BY, historically, could not reference a SELECT-list alias in standard SQL, because grouping happens before SELECT does, so the alias simply doesn't exist yet at that stage. Oracle AI Database 26ai relaxed this for GROUP BY and HAVING as a convenience (covered earlier in this module), but the underlying logical processing order that originally motivated the restriction hasn't changed; the database is still doing the real work in the same sequence, it's just now willing to resolve an alias reference on your behalf when it can.

ORDER BY being last in this sequence is also exactly why it's the only clause that can freely sort by an aggregate, a grouping column, a raw column, or a combination of all three: everything else in the query has already finished running by the time ORDER BY gets involved.

	GROUP BY	ORDER BY
Purpose	Partitions rows into groups for aggregation	Controls the sequence of the final result set
Guarantees output order?	No	Yes
Works with aggregate functions?	Required for per-group aggregates like COUNT, SUM, AVG	Not required; can sort raw or aggregated rows
Can reference a SELECT-list alias?	In Oracle AI Database 26ai, yes (also by position, if enabled)	Yes, in standard SQL generally
Runs relative to WHERE	After WHERE, before HAVING	Last, after everything else including HAVING

Read down that middle row again: "Guarantees output order? No." That single cell is the entire correction this lesson is built around. Everything else in the table, the syntax overlap, the shared vocabulary of "columns," the fact that both can reference the same underlying data, is what makes the two clauses easy to conflate. That one row is where they diverge completely, and it's the row that matters most when you're deciding which clause to reach for.

Both clauses can appear in the same query, and very often should, since a report that's grouped but unordered is usually only half-finished from a reader's perspective:

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;

This groups employees by department, then sorts the resulting summary rows so the largest department appears first. GROUP BY did the aggregation; ORDER BY did the presentation.

Once you've grouped, ORDER BY can sort by whatever makes sense for the report: the grouping column itself, an aggregate, or a combination:

SELECT dno, COUNT(*) AS headcount
FROM employee
GROUP BY dno
ORDER BY dno ASC;

That sorts departments numerically, lowest first, which is the most natural default when the grouping column itself has a meaningful order. Reverse it with DESC for the opposite direction, or sort by the aggregate instead, as shown earlier, when the department number itself isn't the interesting axis and headcount is.

NULL values need special handling in a sort, since a NULL doesn't naturally come "before" or "after" any other value the way two numbers or two strings do. Oracle lets you control this explicitly:

SELECT dno, COUNT(*) AS headcount
FROM employee
GROUP BY dno
ORDER BY dno ASC NULLS LAST;

NULLS LAST pushes any group formed from a NULL department number to the bottom of the result set regardless of sort direction, rather than leaving its position up to the database's default behavior, which differs across engines. This is a small detail, but it's exactly the kind of default assumption that causes a report to look "wrong" to a reviewer without anyone being able to point at a bug in the query logic.

An earlier lesson in this module introduced GROUP BY ALL, along with grouping by a SELECT-list column alias or its ordinal position, all new conveniences in Oracle AI Database 26ai. It's worth being explicit that none of these change anything covered in this lesson. GROUP BY ALL is shorthand for listing every non-aggregated column explicitly; it still groups for aggregation and still comes with no output-order guarantee. Grouping by alias or position is likewise just a different way of naming the same grouping columns, not a different operation. Every one of these features answers "which columns am I grouping by," never "what order should the result come back in." That question still belongs to ORDER BY, in every version of Oracle, with or without the newer syntax.

Recap

You've now covered the actual distinction this lesson set out to teach:

• GROUP BY partitions rows into groups for aggregation and guarantees nothing about their output order
• ORDER BY is the only clause responsible for sorting, and it always runs last
• The apparent overlap comes from both clauses comparing column values, but GROUP BY checks for exact matches to form buckets, while ORDER BY uses column-by-column comparison to break sorting ties
• Every non-aggregated column in the SELECT list must appear in GROUP BY, independent of any sorting concern
• Newer 26ai conveniences like GROUP BY ALL change how you write the grouping columns, not what GROUP BY does with output order

Complete this exercise regarding the GROUP BY clause by clicking the link below.
Group By Clause Exercise