12.11.3. GROUP BY with Hidden Fields
MySQL extends the use of GROUP BY so that you
can use columns or calculations in the SELECT
list that do not appear in the GROUP BY
clause. This stands for “any possible value for this
group.” You can use this to get better performance by
avoiding sorting and grouping on unnecessary items. For example,
you do not need to group on customer.name in
the following query:
SELECT order.custid, customer.name, MAX(payments)
FROM order,customer
WHERE order.custid = customer.custid
GROUP BY order.custid;
In standard SQL, you would have to add
customer.name to the GROUP
BY clause. In MySQL, the name is redundant if you do
not run with the ONLY_FULL_GROUP_BY SQL mode
enabled.
Do not use this feature if the columns you
omit from the GROUP BY part are not unique in
the group! You get unpredictable results.
In some cases, you can use MIN() and
MAX() to obtain a specific column value even
if it isn't unique. The following gives the value of
column from the row containing the smallest
value in the sort column:
SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7)
See Section 3.6.4, “The Rows Holding the Group-wise Maximum of a Certain Field”.
Note that if you are trying to follow standard SQL, you can't
use expressions in GROUP BY clauses. You can
work around this limitation by using an alias for the
expression:
SELECT id,FLOOR(value/100) AS val
FROM tbl_name
GROUP BY id, val;
MySQL does allow expressions in GROUP BY
clauses. For example:
SELECT id,FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);
