DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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