Author: paul
Date: 2006-02-09 15:03:53 +0100 (Thu, 09 Feb 2006)
New Revision: 1248
Log:
r7244@frost: paul | 2006-02-09 07:43:10 -0600
Additional explanation re: UNION and ORDER BY
Modified:
trunk/
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/sql-syntax.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:7240
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2927
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:7244
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2927
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-02-08 18:35:36 UTC (rev 1247)
+++ trunk/refman-4.1/sql-syntax.xml 2006-02-09 14:03:53 UTC (rev 1248)
@@ -7440,18 +7440,53 @@
</para>
<programlisting>
-(SELECT a FROM <replaceable>tbl_name</replaceable> WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
+(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
-(SELECT a FROM <replaceable>tbl_name</replaceable> WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
+(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
</programlisting>
<para>
- <literal>ORDER BY</literal> for individual
- <literal>SELECT</literal> statements within parentheses has an
- effect only when combined with <literal>LIMIT</literal>.
- Otherwise, the <literal>ORDER BY</literal> is optimized away.
+ Use of <literal>ORDER BY</literal> for individual
+ <literal>SELECT</literal> statements implies nothing about the
+ order in which the rows appear in the final result because
+ <literal>UNION</literal> by default produces an unordered set
+ of rows. If <literal>ORDER BY</literal> appears with
+ <literal>LIMIT</literal>, it is used to determine the subset
+ of the selected rows to retrieve for the
+ <literal>SELECT</literal>, but does not affect the order of
+ those rows in the final <literal>UNION</literal> result. If
+ <literal>ORDER BY</literal> appears without
+ <literal>LIMIT</literal> in a <literal>SELECT</literal>, it is
+ optimized away because it will have no effect anyway.
</para>
+ <para>
+ To cause rows in a <literal>UNION</literal> result to consist
+ of the sets of rows retrieved by each
+ <literal>SELECT</literal> one after the other, select an
+ additional column in each <literal>SELECT</literal> to use as
+ a sort column and add an <literal>ORDER BY</literal> following
+ the last <literal>SELECT</literal>:
+ </para>
+
+<programlisting>
+(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
+UNION
+(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
+</programlisting>
+
+ <para>
+ To additionally maintain sort order within individual
+ <literal>SELECT</literal> results, add a secondary column to
+ the <literal>ORDER BY</literal> clause:
+ </para>
+
+<programlisting>
+(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
+UNION
+(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
+</programlisting>
+
<remark>
this is also written in the "known bugs" section
</remark>
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-02-08 18:35:36 UTC (rev 1247)
+++ trunk/refman-5.0/sql-syntax.xml 2006-02-09 14:03:53 UTC (rev 1248)
@@ -7831,18 +7831,53 @@
</para>
<programlisting>
-(SELECT a FROM <replaceable>tbl_name</replaceable> WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
+(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
-(SELECT a FROM <replaceable>tbl_name</replaceable> WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
+(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
</programlisting>
<para>
- <literal>ORDER BY</literal> for individual
- <literal>SELECT</literal> statements within parentheses has an
- effect only when combined with <literal>LIMIT</literal>.
- Otherwise, the <literal>ORDER BY</literal> is optimized away.
+ Use of <literal>ORDER BY</literal> for individual
+ <literal>SELECT</literal> statements implies nothing about the
+ order in which the rows appear in the final result because
+ <literal>UNION</literal> by default produces an unordered set
+ of rows. If <literal>ORDER BY</literal> appears with
+ <literal>LIMIT</literal>, it is used to determine the subset
+ of the selected rows to retrieve for the
+ <literal>SELECT</literal>, but does not affect the order of
+ those rows in the final <literal>UNION</literal> result. If
+ <literal>ORDER BY</literal> appears without
+ <literal>LIMIT</literal> in a <literal>SELECT</literal>, it is
+ optimized away because it will have no effect anyway.
</para>
+ <para>
+ To cause rows in a <literal>UNION</literal> result to consist
+ of the sets of rows retrieved by each
+ <literal>SELECT</literal> one after the other, select an
+ additional column in each <literal>SELECT</literal> to use as
+ a sort column and add an <literal>ORDER BY</literal> following
+ the last <literal>SELECT</literal>:
+ </para>
+
+<programlisting>
+(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
+UNION
+(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
+</programlisting>
+
+ <para>
+ To additionally maintain sort order within individual
+ <literal>SELECT</literal> results, add a secondary column to
+ the <literal>ORDER BY</literal> clause:
+ </para>
+
+<programlisting>
+(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
+UNION
+(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
+</programlisting>
+
<remark>
this is also written in the "known bugs" section
</remark>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-02-08 18:35:36 UTC (rev 1247)
+++ trunk/refman-5.1/sql-syntax.xml 2006-02-09 14:03:53 UTC (rev 1248)
@@ -8412,18 +8412,53 @@
</para>
<programlisting>
-(SELECT a FROM <replaceable>tbl_name</replaceable> WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
+(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
-(SELECT a FROM <replaceable>tbl_name</replaceable> WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
+(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
</programlisting>
<para>
- <literal>ORDER BY</literal> for individual
- <literal>SELECT</literal> statements within parentheses has an
- effect only when combined with <literal>LIMIT</literal>.
- Otherwise, the <literal>ORDER BY</literal> is optimized away.
+ Use of <literal>ORDER BY</literal> for individual
+ <literal>SELECT</literal> statements implies nothing about the
+ order in which the rows appear in the final result because
+ <literal>UNION</literal> by default produces an unordered set
+ of rows. If <literal>ORDER BY</literal> appears with
+ <literal>LIMIT</literal>, it is used to determine the subset
+ of the selected rows to retrieve for the
+ <literal>SELECT</literal>, but does not affect the order of
+ those rows in the final <literal>UNION</literal> result. If
+ <literal>ORDER BY</literal> appears without
+ <literal>LIMIT</literal> in a <literal>SELECT</literal>, it is
+ optimized away because it will have no effect anyway.
</para>
+ <para>
+ To cause rows in a <literal>UNION</literal> result to consist
+ of the sets of rows retrieved by each
+ <literal>SELECT</literal> one after the other, select an
+ additional column in each <literal>SELECT</literal> to use as
+ a sort column and add an <literal>ORDER BY</literal> following
+ the last <literal>SELECT</literal>:
+ </para>
+
+<programlisting>
+(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
+UNION
+(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
+</programlisting>
+
+ <para>
+ To additionally maintain sort order within individual
+ <literal>SELECT</literal> results, add a secondary column to
+ the <literal>ORDER BY</literal> clause:
+ </para>
+
+<programlisting>
+(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
+UNION
+(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
+</programlisting>
+
<remark>
this is also written in the "known bugs" section
</remark>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1248 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 9 Feb |
