CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
JonBrave
JonBrave

Reputation: 4280

MySQL GROUP BY order when no ORDER BY

In MySQL, what order is the resultset if GROUP BY is used but ORDER BY is not specified?

I have inherited code with queries like:

SELECT col1, COUNT(col1)
FROM table
GROUP BY col1

(Actually the SELECT statement can be much more complex than that, involving JOINs etc., but let's start with the base principle.) Note that there is no ORDER BY.

In, say, SQL Server BOL, I am told:

The GROUP BY clause does not order the result set. Use the ORDER BY clause to order the result set.

I have been unable to find a statement as to whether MySQL GROUP BY does or does not promise a particular ordering from GROUP BY alone? If a MySQL reference could be provided to back up any answer that would be most welcome.

Upvotes: 0

Views: 1350

Answers (1)

fancyPants
fancyPants

Reputation: 51908

From the manual:

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL:

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

Relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) is deprecated. To produce a given sort order, use explicit ASC or DESC designators for GROUP BY columns or provide an ORDER BY clause.

Upvotes: 2

Related Questions