(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