GROUP BY behavior when no aggregate functions are present in the SELECT clause

Question

I have a table emp with following structure and data:

name   dept    salary
-----  -----   -----
Jack   a       2
Jill   a       1
Tom    b       2
Fred   b       1

When I execute the following SQL:

SELECT * FROM emp GROUP BY dept

I get the following result:

name   dept    salary
-----  -----   -----
Jill   a       1
Fred   b       1

On what basis did the server decide return Jill and Fred and exclude Jack and Tom?

I am running this query in MySQL.

Note 1: I know the query doesn't make sense on its own. I am trying to debug a problem with a 'GROUP BY' scenario. I am trying to understand the default behavior for this purpose.

Note 2: I am used to writing the SELECT clause same as the GROUP BY clause (minus the aggregate fields). When I came across the behavior described above, I started wondering if I can rely on this for scenarios such as: select the rows from emp table where the salary is the lowest/highest in the dept. E.g.: The SQL statements like this works on MySQL:

SELECT A.*, MIN(A.salary) AS min_salary FROM emp AS A GROUP BY A.dept

I didn't find any material describing why such SQL works, more importantly if I can rely on such behavior consistently. If this is a reliable behavior then I can avoid queries like:

SELECT A.* FROM emp AS A WHERE A.salary = ( 
            SELECT MAX(B.salary) FROM emp B WHERE B.dept = A.dept)

Answer 1

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. 

Answer 2

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.

Answer 3

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

Answer 4

You can put a:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

before your query to enforce SQL standard GROUP BY behavior

Answer 5

I find that the best thing to do is to consider this type of query unsupported. In most other database systems, you can't include columns that aren't either in the GROUP BY clause or in an aggregate function in the HAVING, SELECT or ORDER BY clauses.

Instead, consider that your query reads:

SELECT ANY(name), dept, ANY(salary)
FROM emp 
GROUP BY dept;

...since this is what's going on.

Hope this helps....

Answer 6

I think ANSI SQL requires that the select includes only fields from the GROUP BY clause, plus aggregate functions. This behaviour of MySQL looks like returns some row, possibly the last one the server read, or any row it had at hand, but don't rely on that.

Answer 7

This would select the most recent row for each person:

SELECT * FROM emp
WHERE ID IN
(
    SELECT
        MAX(ID) AS ID
    FROM
        emp
    GROUP BY
        name
)

Answer 8

If you are grouping by department does it matter about the other data? I know Sql Server will not even allow this query. If there is a possibility of this sounds like there might be other issues.

Answer 9

Try using ORDER BY to pick the row that you want.

SELECT * FROM emp GROUP BY dept ORDER BY name ASC;

Will return the following:

name   dept    salary
-----  -----   -----
jack   a       2
fred   b       1