Author: paul
Date: 2006-01-26 20:08:01 +0100 (Thu, 26 Jan 2006)
New Revision: 1048
Log:
r2558@kite-hub: paul | 2006-01-26 13:07:37 -0600
Standard SQL allows expressions in ORDER BY. Don't claim otherwise.
Modified:
trunk/
trunk/refman-4.1/functions.xml
trunk/refman-5.0/functions.xml
trunk/refman-5.1/functions.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6690
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2556
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6690
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2558
Modified: trunk/refman-4.1/functions.xml
===================================================================
--- trunk/refman-4.1/functions.xml 2006-01-26 18:26:10 UTC (rev 1047)
+++ trunk/refman-4.1/functions.xml 2006-01-26 19:08:01 UTC (rev 1048)
@@ -14803,6 +14803,16 @@
<secondary>extensions to standard SQL</secondary>
</indexterm>
+ <indexterm>
+ <primary><literal>ONLY_FULL_GROUP_BY</literal></primary>
+ <secondary>SQL mode</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>SQL mode</primary>
+ <secondary><literal>ONLY_FULL_GROUP_BY</literal></secondary>
+ </indexterm>
+
<para>
MySQL extends the use of <literal>GROUP BY</literal> so that you
can use columns or calculations in the <literal>SELECT</literal>
@@ -14815,17 +14825,18 @@
</para>
<programlisting>
-mysql> <userinput>SELECT order.custid, customer.name, MAX(payments)</userinput>
- -> <userinput>FROM order,customer</userinput>
- -> <userinput>WHERE order.custid = customer.custid</userinput>
- -> <userinput>GROUP BY order.custid;</userinput>
+SELECT order.custid, customer.name, MAX(payments)
+ FROM order,customer
+ WHERE order.custid = customer.custid
+ GROUP BY order.custid;
</programlisting>
<para>
In standard SQL, you would have to add
<literal>customer.name</literal> to the <literal>GROUP
BY</literal> clause. In MySQL, the name is redundant if you do
- not run in ANSI mode.
+ not run with the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode
+ enabled.
</para>
<para>
@@ -14851,16 +14862,6 @@
</para>
<indexterm>
- <primary><literal>ORDER BY</literal></primary>
- <secondary>aliases in</secondary>
- </indexterm>
-
- <indexterm>
- <primary>aliases</primary>
- <secondary>in <literal>ORDER BY</literal> clauses</secondary>
- </indexterm>
-
- <indexterm>
<primary><literal>GROUP BY</literal></primary>
<secondary>aliases in</secondary>
</indexterm>
@@ -14903,6 +14904,36 @@
mysql> <userinput>SELECT id, FLOOR(value/100) FROM <replaceable>tbl_name</replaceable> ORDER BY RAND();</userinput>
</programlisting>
+ <para>
+ 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
+ <literal>GROUP BY</literal> clauses. You can work around this
+ limitation by using an alias for the expression:
+ </para>
+
+<programlisting>
+SELECT id,FLOOR(value/100) AS val
+ FROM <replaceable>tbl_name</replaceable>
+ GROUP BY id, val;
+</programlisting>
+
+ <para>
+ In MySQL 3.23 and up, aliases are unnecessary and MySQL does
+ allow expressions in <literal>GROUP BY</literal> clauses. For
+ example:
+ </para>
+
+<programlisting>
+SELECT id,FLOOR(value/100)
+ FROM <replaceable>tbl_name</replaceable>
+ GROUP BY id, FLOOR(value/100);
+</programlisting>
+
+ <para>
+ Before MySQL 3.23, MySQL also requires use of aliases to refer
+ to expressions in <literal>ORDER BY</literal> clauses.
+ </para>
+
</section>
</section>
Modified: trunk/refman-5.0/functions.xml
===================================================================
--- trunk/refman-5.0/functions.xml 2006-01-26 18:26:10 UTC (rev 1047)
+++ trunk/refman-5.0/functions.xml 2006-01-26 19:08:01 UTC (rev 1048)
@@ -15035,6 +15035,16 @@
<secondary>extensions to standard SQL</secondary>
</indexterm>
+ <indexterm>
+ <primary><literal>ONLY_FULL_GROUP_BY</literal></primary>
+ <secondary>SQL mode</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>SQL mode</primary>
+ <secondary><literal>ONLY_FULL_GROUP_BY</literal></secondary>
+ </indexterm>
+
<para>
MySQL extends the use of <literal>GROUP BY</literal> so that you
can use columns or calculations in the <literal>SELECT</literal>
@@ -15047,17 +15057,18 @@
</para>
<programlisting>
-mysql> <userinput>SELECT order.custid, customer.name, MAX(payments)</userinput>
- -> <userinput>FROM order,customer</userinput>
- -> <userinput>WHERE order.custid = customer.custid</userinput>
- -> <userinput>GROUP BY order.custid;</userinput>
+SELECT order.custid, customer.name, MAX(payments)
+ FROM order,customer
+ WHERE order.custid = customer.custid
+ GROUP BY order.custid;
</programlisting>
<para>
In standard SQL, you would have to add
<literal>customer.name</literal> to the <literal>GROUP
BY</literal> clause. In MySQL, the name is redundant if you do
- not run in ANSI mode.
+ not run with the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode
+ enabled.
</para>
<para>
@@ -15083,16 +15094,6 @@
</para>
<indexterm>
- <primary><literal>ORDER BY</literal></primary>
- <secondary>aliases in</secondary>
- </indexterm>
-
- <indexterm>
- <primary>aliases</primary>
- <secondary>in <literal>ORDER BY</literal> clauses</secondary>
- </indexterm>
-
- <indexterm>
<primary><literal>GROUP BY</literal></primary>
<secondary>aliases in</secondary>
</indexterm>
@@ -15113,25 +15114,26 @@
<para>
Note that if you are trying to follow standard SQL, you can't
- use expressions in <literal>GROUP BY</literal> or <literal>ORDER
- BY</literal> clauses. You can work around this limitation by
- using an alias for the expression:
+ use expressions in <literal>GROUP BY</literal> clauses. You can
+ work around this limitation by using an alias for the
+ expression:
</para>
<programlisting>
-mysql> <userinput>SELECT id,FLOOR(value/100) AS val </userinput>
- -> <userinput>FROM <replaceable>tbl_name</replaceable></userinput>
- -> <userinput>GROUP BY id, val ORDER BY val;</userinput>
+SELECT id,FLOOR(value/100) AS val
+ FROM <replaceable>tbl_name</replaceable>
+ GROUP BY id, val;
</programlisting>
<para>
- However, MySQL allows you to use expressions in <literal>GROUP
- BY</literal> and <literal>ORDER BY</literal> clauses. For
- example:
+ MySQL does allow expressions in <literal>GROUP BY</literal>
+ clauses. For example:
</para>
<programlisting>
-mysql> <userinput>SELECT id, FLOOR(value/100) FROM <replaceable>tbl_name</replaceable> ORDER BY RAND();</userinput>
+SELECT id,FLOOR(value/100)
+ FROM <replaceable>tbl_name</replaceable>
+ GROUP BY id, FLOOR(value/100);
</programlisting>
</section>
Modified: trunk/refman-5.1/functions.xml
===================================================================
--- trunk/refman-5.1/functions.xml 2006-01-26 18:26:10 UTC (rev 1047)
+++ trunk/refman-5.1/functions.xml 2006-01-26 19:08:01 UTC (rev 1048)
@@ -15418,6 +15418,16 @@
<secondary>extensions to standard SQL</secondary>
</indexterm>
+ <indexterm>
+ <primary><literal>ONLY_FULL_GROUP_BY</literal></primary>
+ <secondary>SQL mode</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>SQL mode</primary>
+ <secondary><literal>ONLY_FULL_GROUP_BY</literal></secondary>
+ </indexterm>
+
<para>
MySQL extends the use of <literal>GROUP BY</literal> so that you
can use columns or calculations in the <literal>SELECT</literal>
@@ -15430,17 +15440,18 @@
</para>
<programlisting>
-mysql> <userinput>SELECT order.custid, customer.name, MAX(payments)</userinput>
- -> <userinput>FROM order,customer</userinput>
- -> <userinput>WHERE order.custid = customer.custid</userinput>
- -> <userinput>GROUP BY order.custid;</userinput>
+SELECT order.custid, customer.name, MAX(payments)
+ FROM order,customer
+ WHERE order.custid = customer.custid
+ GROUP BY order.custid;
</programlisting>
<para>
In standard SQL, you would have to add
<literal>customer.name</literal> to the <literal>GROUP
BY</literal> clause. In MySQL, the name is redundant if you do
- not run in ANSI mode.
+ not run with the <literal>ONLY_FULL_GROUP_BY</literal> SQL mode
+ enabled.
</para>
<para>
@@ -15466,16 +15477,6 @@
</para>
<indexterm>
- <primary><literal>ORDER BY</literal></primary>
- <secondary>aliases in</secondary>
- </indexterm>
-
- <indexterm>
- <primary>aliases</primary>
- <secondary>in <literal>ORDER BY</literal> clauses</secondary>
- </indexterm>
-
- <indexterm>
<primary><literal>GROUP BY</literal></primary>
<secondary>aliases in</secondary>
</indexterm>
@@ -15496,25 +15497,26 @@
<para>
Note that if you are trying to follow standard SQL, you can't
- use expressions in <literal>GROUP BY</literal> or <literal>ORDER
- BY</literal> clauses. You can work around this limitation by
- using an alias for the expression:
+ use expressions in <literal>GROUP BY</literal> clauses. You can
+ work around this limitation by using an alias for the
+ expression:
</para>
<programlisting>
-mysql> <userinput>SELECT id,FLOOR(value/100) AS val </userinput>
- -> <userinput>FROM <replaceable>tbl_name</replaceable></userinput>
- -> <userinput>GROUP BY id, val ORDER BY val;</userinput>
+SELECT id,FLOOR(value/100) AS val
+ FROM <replaceable>tbl_name</replaceable>
+ GROUP BY id, val;
</programlisting>
<para>
- However, MySQL allows you to use expressions in <literal>GROUP
- BY</literal> and <literal>ORDER BY</literal> clauses. For
- example:
+ MySQL does allow expressions in <literal>GROUP BY</literal>
+ clauses. For example:
</para>
<programlisting>
-mysql> <userinput>SELECT id, FLOOR(value/100) FROM <replaceable>tbl_name</replaceable> ORDER BY RAND();</userinput>
+SELECT id,FLOOR(value/100)
+ FROM <replaceable>tbl_name</replaceable>
+ GROUP BY id, FLOOR(value/100);
</programlisting>
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1048 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 26 Jan |
