(mysql.info) group-by-hidden-fields
Info Catalog
(mysql.info) group-by-modifiers
(mysql.info) group-by-functions-and-modifiers
12.10.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 example-maximum-column-group-row.
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);
Info Catalog
(mysql.info) group-by-modifiers
(mysql.info) group-by-functions-and-modifiers
automatically generated byinfo2html