Documentation Source Text

Check-in [7b8d43a8ef]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Clarification of the role of ORDER BY and LIMIT in compound SELECT statements.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7b8d43a8ef2404ddcc2fa5ee79873e8b3f39af4e
User & Date: drh 2014-01-30 11:08:58
Context
2014-01-30
11:15
Additional hyperlinks to "compound SELECT", "ORDER BY" and "LIMIT". check-in: 1c8ccd6d21 user: drh tags: trunk
11:08
Clarification of the role of ORDER BY and LIMIT in compound SELECT statements. check-in: 7b8d43a8ef user: drh tags: trunk
2014-01-29
17:47
Update News and the change log. Bring the release date forward to 2014-02-03. Fix typos. Move older news entries out to oldnews.in. check-in: ea6e1d11de user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

  3898   3898   <p>^(When three or more simple SELECTs are connected into a compound SELECT,
  3899   3899   they group from left to right. In other words, if "A", "B" and "C" are all
  3900   3900   simple SELECT statements, (A op B op C) is processed as ((A op B) op C).)^
  3901   3901   
  3902   3902   </p>
  3903   3903   
  3904   3904   <tcl>hd_fragment orderby {order by}</tcl>
  3905         -<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>
         3905  +<h3>ORDER BY clauses</h3>
  3906   3906   
  3907   3907   <p>If a SELECT statement that returns more than one row does not have an
  3908   3908   ORDER BY clause, the order in which the rows are returned is undefined.
  3909   3909   Or, if a SELECT statement does have an ORDER BY clause, then the list of
  3910   3910   expressions attached to the ORDER BY determine the order in which rows
  3911         -are returned to the user. ^Rows are first sorted based on the results of
         3911  +are returned to the user.
         3912  +In a compound SELECT statement, only the last (right-most) SELECT may have
         3913  +an ORDER BY clause and that ORDER BY clause will apply across all elements of
         3914  +the compound.
         3915  +
         3916  +
         3917  +<p>^Rows are first sorted based on the results of
  3912   3918   evaluating the left-most expression in the ORDER BY list, then ties are broken
  3913   3919   by evaluating the second left-most expression and so on. The order in which
  3914   3920   two rows for which all ORDER BY expressions evaluate to equal values are
  3915   3921   returned is undefined. ^Each ORDER BY expression may be optionally followed
  3916   3922   by one of the keywords ASC (smaller values are returned first) or DESC (larger
  3917   3923   values are returned first). ^If neither ASC or DESC are specified, rows
  3918   3924   are sorted in ascending (smaller values first) order by default.
................................................................................
  3961   3967   the second or third rules above. If a match is found, the search stops and
  3962   3968   the expression is handled as an alias for the result column that it has been
  3963   3969   matched against. Otherwise, the next SELECT to the right is tried, and so on.)^
  3964   3970   ^If no matching expression can be found in the result columns of any
  3965   3971   constituent SELECT, it is an error. ^Each term of the ORDER BY clause is
  3966   3972   processed separately and may be matched against result columns from different
  3967   3973   SELECT statements in the compound.</p>
         3974  +
         3975  +<tcl>hd_fragment limitoffset {LIMIT}</tcl>
         3976  +<h3>LIMIT and OFFSET clauses</h3>
  3968   3977   
  3969   3978   <p>The LIMIT clause is used to place an upper bound on the number of rows
  3970         -returned by a SELECT statement. ^Any scalar expression may be used in the 
         3979  +returned by the entire SELECT statement.  In a compound SELECT, only the
         3980  +last (right-most) SELECT may contain a LIMIT clause.  In a compound SELECT, 
         3981  +the LIMIT clause applies to the entire compound, not just the final SELECT.
         3982  +
         3983  +<p>^Any scalar expression may be used in the 
  3971   3984   LIMIT clause, so long as it evaluates to an integer or a value that can be
  3972   3985   losslessly converted to an integer. ^If the expression evaluates to a NULL 
  3973   3986   value or any other value that cannot be losslessly converted to an integer, an
  3974   3987   error is returned. ^If the LIMIT expression evaluates to a negative value,
  3975   3988   then there is no upper bound on the number of rows returned. ^Otherwise, the
  3976   3989   SELECT returns the first N rows of its result set only, where N is the value
  3977   3990   that the LIMIT expression evaluates to. ^Or, if the SELECT statement would