Documentation Source Text

Check-in [7b8d43a8ef]

Overview
Comment: Clarification of the role of ORDER BY and LIMIT in compound SELECT statements. family | ancestors | descendants | both | files | file ages | folders 7b8d43a8ef2404ddcc2fa5ee79873e8b3f39af4e 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

Changes to pages/lang.in.

 ```3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 .... 3961 3962 3963 3964 3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 ``` ```

^(When three or more simple SELECTs are connected into a compound SELECT, 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).)^

hd_fragment orderby {order by}

ORDER BY and LIMIT/OFFSET Clauses

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. ................................................................................ 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.)^ ^If no matching expression can be found in the result columns of any constituent SELECT, it is an error. ^Each term of the ORDER BY clause is processed separately and may be matched against result columns from different SELECT statements in the compound.

The LIMIT clause is used to place an upper bound on the number of rows returned by a SELECT statement. ^Any scalar expression may be used in the LIMIT clause, so long as it evaluates to an integer or a value that can be losslessly converted to an integer. ^If the expression evaluates to a NULL value or any other value that cannot be losslessly converted to an integer, an error is returned. ^If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned. ^Otherwise, the SELECT returns the first N rows of its result set only, where N is the value that the LIMIT expression evaluates to. ^Or, if the SELECT statement would ``` ``` | > > > > > > | > > > > > > > | ``` ```3898 3899 3900 3901 3902 3903 3904 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 3923 3924 .... 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 3982 3983 3984 3985 3986 3987 3988 3989 3990 ``` ```

^(When three or more simple SELECTs are connected into a compound SELECT, 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).)^

hd_fragment orderby {order by}

ORDER BY clauses

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. In a compound SELECT statement, only the last (right-most) SELECT may have an ORDER BY clause and that ORDER BY clause will apply across all elements of the compound.

^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. ................................................................................ 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.)^ ^If no matching expression can be found in the result columns of any constituent SELECT, it is an error. ^Each term of the ORDER BY clause is processed separately and may be matched against result columns from different SELECT statements in the compound.

hd_fragment limitoffset {LIMIT}

LIMIT and OFFSET clauses

The LIMIT clause is used to place an upper bound on the number of rows returned by the entire SELECT statement. In a compound SELECT, only the last (right-most) SELECT may contain a LIMIT clause. In a compound SELECT, the LIMIT clause applies to the entire compound, not just the final SELECT.

^Any scalar expression may be used in the LIMIT clause, so long as it evaluates to an integer or a value that can be losslessly converted to an integer. ^If the expression evaluates to a NULL value or any other value that cannot be losslessly converted to an integer, an error is returned. ^If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned. ^Otherwise, the SELECT returns the first N rows of its result set only, where N is the value that the LIMIT expression evaluates to. ^Or, if the SELECT statement would ```