(mysql.info.gz) GROUP-BY-hidden-fields
Info Catalog
(mysql.info.gz) GROUP-BY-Modifiers
(mysql.info.gz) Group by functions and modifiers
12.9.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 don't 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 don't need to group on
`customer.name' in the following query:
mysql> 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 don't run in ANSI
mode.
Do _not_ use this feature if the columns you omit from the `GROUP BY'
part are not unique in the group! You will 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)
example-Maximum-column-group-row.
Note that if you are using MySQL 3.22 (or earlier) or if you are trying
to follow standard SQL, you can't use expressions in `GROUP BY' or
`ORDER BY' clauses. You can work around this limitation by using an
alias for the expression:
mysql> SELECT id,FLOOR(value/100) AS val FROM TBL_NAME
-> GROUP BY id, val ORDER BY val;
In MySQL 3.23 and up, aliases are unnecessary. You can use expressions
in `GROUP BY' and `ORDER BY' clauses. For example:
mysql> SELECT id, FLOOR(value/100) FROM TBL_NAME ORDER BY RAND();
Info Catalog
(mysql.info.gz) GROUP-BY-Modifiers
(mysql.info.gz) Group by functions and modifiers
automatically generated byinfo2html