Documentation Source Text

Check-in [095cad2463]
Login

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

Overview
Comment:Changes to make the documentation for ORDER BY more testable.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 095cad2463657926b41cce5069409478ee227ca1
User & Date: dan 2010-09-15 18:54:03
Context
2010-09-15
22:17
Updates to the documentation for the new sqlite3_soft_heap_limit64() interface. check-in: 5d170249d2 user: drh tags: trunk
18:54
Changes to make the documentation for ORDER BY more testable. check-in: 095cad2463 user: dan tags: trunk
2010-09-14
19:10
Adding additional comparison operators to the list in datatype3.html. check-in: a43ee1e871 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/lang.in.

2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880



2881
2882
2883



2884
2885
2886
2887
2888
2889


2890
2891
2892
















2893
2894










2895
2896
2897
2898
2899



2900

2901
2902

2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
they group from left to right. In other words, if "A", "B" and "C" are all
simple SELECT statements, (A op B op C) is processed as ((A op B) op C).)^

</p>

<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>

<p>^The ORDER BY clause causes the output rows to be sorted.  
^The argument to ORDER BY is a list of expressions that are used as the
key for the sort.  ^The expressions do not have to be part of the
result for a simple SELECT, but in a compound SELECT each sort
expression must exactly match one of the result columns.  ^Each



sort expression may be optionally followed by a COLLATE keyword and
the name of a collating function used for ordering text and/or
keywords ASC or DESC to specify the sort order.</p>




<p>Each term of an ORDER BY expression is processed as follows:</p>

<ol>
<li><p>^If the ORDER BY expression is a constant integer K then the
output is ordered by the K-th column of the result set.</p></li>


<li><p>^If the ORDER BY expression is an identifier and one of the
output columns has an alias by the same name, then the output is
ordered by the identified column.</p></li>
















<li><p>^Otherwise, the ORDER BY expression is evaluated and the output 
is ordered by the value of that expression.</p></li>










</ol>

<p>^In a compound SELECT statement, the third ORDER BY matching rule
requires that the expression be identical to one of the columns in
the result set.  ^(The three rules are first applied to the left-most



SELECT in the compound.  If a match is found, the search stops.  Otherwise,

the next SELECT to the right is tried.  This continues until a match
is found.)^  ^Each term of the ORDER BY clause is processed separately 

and may come from different SELECT statements in the compound.</p>

<p>^The LIMIT clause places an upper bound on the number of rows
returned in the result.  ^A negative LIMIT indicates no upper bound.
^The optional OFFSET following LIMIT specifies how many
rows to skip at the beginning of the result set.
^In a compound query, the LIMIT clause may only appear on the
final SELECT statement.
^The limit is applied to the entire query not
to the individual SELECT statement to which it is attached.
^Note that if the OFFSET keyword is used in the LIMIT clause, then the
limit is the first number and the offset is the second number.  ^If a
comma is used instead of the OFFSET keyword, then the offset is the
first number and the limit is the second number.  This seeming
contradiction is intentional - it maximizes compatibility with legacy
SQL database systems.
</p>


<tcl>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}








|
|
|
|
|
>
>
>
|
<
<
>
>
>

|



|
>
>
|
<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
>
>
>
>
>
>
>
>
>
>


|
<
<
>
>
>
|
>
|
|
>
|

|
|
<
|
|
<
|
|
|
<
|
|
|
|







2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884


2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896


2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907
2908
2909
2910
2911
2912
2913

2914
2915
2916
2917
2918
2919
2920
2921
2922
2923
2924
2925
2926


2927
2928
2929
2930
2931
2932
2933
2934
2935
2936
2937
2938

2939
2940

2941
2942
2943

2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
they group from left to right. In other words, if "A", "B" and "C" are all
simple SELECT statements, (A op B op C) is processed as ((A op B) op C).)^

</p>

<h3>ORDER BY and LIMIT/OFFSET Clauses</h3>

<p>If a SELECT statement that returns more than one row does not have an
ORDER BY clause, the order in which the rows are returned is undefined.
Or, if a SELECT statement does have an ORDER BY clause, then the list of
expressions attached to the ORDER BY determine the order in which rows
are returned to the user. ^Rows are first sorted based on the results of
evaluating the left-most expression in the ORDER BY list, then ties are broken
by evaluating the second left-most expression and so on. The order in which
two rows for which all ORDER BY expressions evaluate to equal values are
returned is undefined. ^Each ORDER BY expression may be optionally followed


by one of the keywords ASC (smaller values are returned first) or DESC (larger
values are returned first). ^If neither ASC or DESC are specified, rows
are sorted in ascending (smaller values first) order by default.

<p>Each ORDER BY expression is processed as follows:</p>

<ol>
<li><p>^If the ORDER BY expression is a constant integer K then the
expression is considered an alias for the K-th column of the result set
(columns are numbered from left to right starting with 1).

<li><p>^If the ORDER BY expression is an identifier that corresponds to


the alias of one of the output columns, then the expression is considered
an alias for that column.

<li><p>^Otherwise, if the ORDER BY expression is any other expression, it 
is evaluated and the the returned value used to order the output rows. ^If
the SELECT statement is a simple SELECT, then an ORDER BY may contain any
arbitrary expressions. ^However, if the SELECT is a compound SELECT, then
ORDER BY expressions that are not aliases to output columns must be exactly
the same as an expression used as an output column.
</ol>

<p>^For the purposes of sorting rows, values are compared in the same way
as for [comparison expressions]. The collation sequence used to compare
two text values is determined as follows:

<ol>
  <li><p>^If the ORDER BY expression is assigned a collation sequence using

  the postfix [COLLATE operator], then the specified collation sequence is
  used.
  <li><p>^Otherwise, if the ORDER BY expression is an alias to an expression
  that has been assigned a collation sequence using the postfix 
  [COLLATE operator], then the collation sequence assigned to the aliased
  expression is used.
  <li><p>^Otherwise, if the ORDER BY expression is a column or an alias of
  an expression that is a column, then the default collation sequence for
  the column is used. 
  <li><p>^Otherwise, the [BINARY] collation sequence is used.
</ol>

<p>^In a compound SELECT statement, all ORDER BY expressions are handled


as aliases for one of the result columns of the compound SELECT.
^(If an ORDER BY expression is not an integer alias, then SQLite searches
the left-most SELECT in the compound for a result column that matches either
the second or third rules above. If a match is found, the search stops and
the expression is handled as an alias for the result column that it has been
matched against. Otherwise, the next SELECT to the right is tried, and so on.
This continues until a match is found.)^ ^Each term of the ORDER BY clause is
processed separately and may be matched against result columns from different
SELECT statements in the compound.</p>

<p>^The LIMIT clause places an upper bound on the number of rows returned in
the result. ^A negative LIMIT indicates no upper bound. ^The optional OFFSET

following LIMIT specifies how many rows to skip at the beginning of the result
set.  ^In a compound query, the LIMIT clause may only appear on the final

SELECT statement. ^The limit is applied to the entire query not to the
individual SELECT statement to which it is attached.  ^Note that if the OFFSET
keyword is used in the LIMIT clause, then the limit is the first number and the

offset is the second number.  ^If a comma is used instead of the OFFSET
keyword, then the offset is the first number and the limit is the second
number.  This seeming contradiction is intentional - it maximizes compatibility
with legacy SQL database systems.
</p>


<tcl>
##############################################################################
Section UPDATE update {UPDATE *UPDATEs}