MySQL extends the use of GROUP BY
so that you
can use nonaggregated columns or calculations in the
SELECT
list that do not appear in
the GROUP BY
clause. You can use this feature
to get better performance by avoiding unnecessary column sorting
and grouping. 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.
When using this feature, all rows in each group should have the
same values for the columns that are ommitted 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.
A similar MySQL extension applies to the
HAVING
clause. The SQL standard does not
allow the HAVING
clause to name any column
that is not found in the GROUP BY
clause if
it is not enclosed in an aggregate function. MySQL allows the
use of such columns to simplify calculations. This extension
assumes that the nongrouped columns will have the same
group-wise values. Otherwise, the result is indeterminate.
If the ONLY_FULL_GROUP_BY
SQL
mode is enabled, the MySQL extension to GROUP
BY
does not apply to the
SELECT
. That is, columns not
named in the GROUP BY
clause cannot be used
in the SELECT
list if not used in
an aggregate function.
The select list extension also applies to ORDER
BY
. That is, you can use nonaggregated columns or
calculations in the ORDER BY
clause that do
not appear in the GROUP BY
clause. This
extension does not apply if the
ONLY_FULL_GROUP_BY
SQL mode is
enabled.
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 Column”.
Note that if you are using MySQL 3.22 (or earlier) or if you are
trying to follow standard SQL, you cannot 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();
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
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;
In MySQL 3.23 and up, aliases are unnecessary and MySQL does
allow expressions in GROUP BY
clauses. For
example:
SELECT id,FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);
Before MySQL 3.23, MySQL also requires use of aliases to refer
to expressions in ORDER BY
clauses.
User Comments
Add your own comment.