You are viewing a plain text version of this content. The canonical link for it is here.
Posted to codereview@trafodion.apache.org by liuyu000 <gi...@git.apache.org> on 2018/02/28 10:31:11 UTC
[GitHub] trafodion pull request #1458: [TRAFODION-2973] Add syntax and examples of *[...
GitHub user liuyu000 opened a pull request:
https://github.com/apache/trafodion/pull/1458
[TRAFODION-2973] Add syntax and examples of *[LAST N] (SELECT Statement)* in *Trafodion SQL Reference Manual*
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/liuyu000/trafodion LastN_SELECTStatement
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1458.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1458
----
commit 83765f2e722c86af7c8a4e2a10f25741c2a05aef
Author: liu.yu <yu...@...>
Date: 2018-02-28T10:28:45Z
Add syntax and examples of *[LAST N] (SELECT Statement)* in *Trafodion SQL Reference Manual*
----
---
[GitHub] trafodion pull request #1458: [TRAFODION-2973] Add syntax and examples of *[...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/trafodion/pull/1458
---
[GitHub] trafodion pull request #1458: [TRAFODION-2973] Add syntax and examples of *[...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1458#discussion_r171317972
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc ---
@@ -6989,20 +6989,22 @@ By abstracting the complicated parts of the query into simpler, separate and log
+
specifies the unique name of the CTE to be created, which is a valid SQL identifier with a maximum of 128 characters. Duplicate names are not allowed in a single with-clause.
-* `"[" ANY _N_ "]" | "[" FIRST _N_ "]"`
+* `"[" ANY _N_ "]" | "[" FIRST _N_ "]" | "[" LAST _N_ "]" `
+
specifies that _N_ rows are to be returned (assuming the table has at least _N_ rows and that the qualification
criteria specified in the WHERE clause, if any, would select at least _N_ rows) and you do not care which _N_ rows
are chosen (out of the qualified rows) to actually be returned.
+
-_You must enclose ANY N or FIRST N in square brackets ([])._ The quotation marks ("") around each square bracket in
+You must enclose `ANY _N_`, `FIRST _N_` or `LAST _N_` in square brackets ([]). The quotation marks ("") around each square bracket in
the syntax diagram indicate that the bracket is a required character that you must type as shown (for example, [ANY 10]
or [FIRST 5]). Do not include quotation marks in ANY or FIRST clauses.
+
[FIRST _N_] is different from [ANY _N_] only if you use ORDER BY on any of the columns in the select list to sort the
result table of the SELECT statement. _N_ is an unsigned numeric literal with no scale. If _N_ is greater than the number
of rows in the table, all rows are returned. [ANY _N_] and [FIRST _N_] are disallowed in nested SELECT statements and on
either side of a UNION operation.
++
+`[LAST _N_]` performs a full table scan and calculates elapsed time. The _N_ must be 0 or 1. `[LAST _0_]` does not return any rows. `[LAST _1_]` returns only the last qualified row.
--- End diff --
"... performs a full table scan ..." This is not quite correct. Our query plan might have key predicates that limit us to less than a full table scan, for example. A more accurate description might be "... performs the entire query ...".
---
[GitHub] trafodion pull request #1458: [TRAFODION-2973] Add syntax and examples of *[...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1458#discussion_r171451317
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc ---
@@ -6989,20 +6989,22 @@ By abstracting the complicated parts of the query into simpler, separate and log
+
specifies the unique name of the CTE to be created, which is a valid SQL identifier with a maximum of 128 characters. Duplicate names are not allowed in a single with-clause.
-* `"[" ANY _N_ "]" | "[" FIRST _N_ "]"`
+* `"[" ANY _N_ "]" | "[" FIRST _N_ "]" | "[" LAST _N_ "]" `
+
specifies that _N_ rows are to be returned (assuming the table has at least _N_ rows and that the qualification
criteria specified in the WHERE clause, if any, would select at least _N_ rows) and you do not care which _N_ rows
are chosen (out of the qualified rows) to actually be returned.
+
-_You must enclose ANY N or FIRST N in square brackets ([])._ The quotation marks ("") around each square bracket in
+You must enclose `ANY _N_`, `FIRST _N_` or `LAST _N_` in square brackets ([]). The quotation marks ("") around each square bracket in
the syntax diagram indicate that the bracket is a required character that you must type as shown (for example, [ANY 10]
or [FIRST 5]). Do not include quotation marks in ANY or FIRST clauses.
+
[FIRST _N_] is different from [ANY _N_] only if you use ORDER BY on any of the columns in the select list to sort the
result table of the SELECT statement. _N_ is an unsigned numeric literal with no scale. If _N_ is greater than the number
of rows in the table, all rows are returned. [ANY _N_] and [FIRST _N_] are disallowed in nested SELECT statements and on
either side of a UNION operation.
++
+`[LAST _N_]` performs a full table scan and calculates elapsed time. The _N_ must be 0 or 1. `[LAST _0_]` does not return any rows. `[LAST _1_]` returns only the last qualified row.
--- End diff --
Thanks for your explanations! Your comments have been incorporated. :)
---