GROUP BY behavior when no aggregate functions are present in the SELECT clause
Read MySQL documentation on this particular point.
In a nutshell, MySQL allows omitting some columns from the GROUP BY, for performance purposes, however this works only if the omitted columns all have the same value (within a grouping), otherwise, the value returned by the query are indeed indeterminate, as properly guessed by others in this post. To be sure adding an ORDER BY clause would not re-introduce any form of deterministic behavior.
Although not at the core of the issue, this example shows how using * rather than an explicit enumeration of desired columns is often a bad idea.
Excerpt from MySQL 5.0 documentation:
When using this feature, all rows in each group should have the same values for the columns that are omitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.
As far as I know, for your purposes the specific rows returned can be considered to be random.
Ordering only takes place after GROUP BY
is done
This is a bit late, but I'll put this up for future reference.
The GROUP BY takes the first row that has a duplicate and discards any rows that match after it in the result set. So if Jack and Tom have the same department, whoever appears first in a normal SELECT will be the resulting row in the GROUP BY.
If you want to control what appears first in the list, you need to do an ORDER BY. However, SQL does not allow ORDER BY to come before GROUP BY, as it will throw an exception. The best workaround for this issue is to do the ORDER BY in a subquery and then a GROUP BY in the outer query. Here's an example:
SELECT * FROM (SELECT * FROM emp ORDER BY name) as foo GROUP BY dept
This is the best performing technique I've found. I hope this helps someone out.