diff options
| author | Alvaro Herrera <alvherre@alvh.no-ip.org> | 2020-04-07 16:22:13 -0400 |
|---|---|---|
| committer | Alvaro Herrera <alvherre@alvh.no-ip.org> | 2020-04-07 16:22:13 -0400 |
| commit | 357889eb17bb9c9336c4f324ceb1651da616fe57 (patch) | |
| tree | ef30422c7a6ebc2844492bd045ffbfeb30e54516 /doc/src | |
| parent | 26a944cf29ba67bb49f42656dd2be98fe2485f5f (diff) | |
| download | postgresql-357889eb17bb9c9336c4f324ceb1651da616fe57.tar.gz postgresql-357889eb17bb9c9336c4f324ceb1651da616fe57.zip | |
Support FETCH FIRST WITH TIES
WITH TIES is an option to the FETCH FIRST N ROWS clause (the SQL
standard's spelling of LIMIT), where you additionally get rows that
compare equal to the last of those N rows by the columns in the
mandatory ORDER BY clause.
There was a proposal by Andrew Gierth to implement this functionality in
a more powerful way that would yield more features, but the other patch
had not been finished at this time, so we decided to use this one for
now in the spirit of incremental development.
Author: Surafel Temesgen <surafel3000@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Discussion: https://postgr.es/m/CALAY4q9ky7rD_A4vf=FVQvCGngm3LOes-ky0J6euMrg=_Se+ag@mail.gmail.com
Discussion: https://postgr.es/m/87o8wvz253.fsf@news-spur.riddles.org.uk
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 15 |
1 files changed, 9 insertions, 6 deletions
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 691e4028030..2b11c38087f 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -44,7 +44,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ] [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ] - [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ] + [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] <phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase> @@ -1438,7 +1438,7 @@ OFFSET <replaceable class="parameter">start</replaceable> which <productname>PostgreSQL</productname> also supports. It is: <synopsis> OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS } -FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY +FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } { ONLY | WITH TIES } </synopsis> In this syntax, the <replaceable class="parameter">start</replaceable> or <replaceable class="parameter">count</replaceable> value is required by @@ -1448,10 +1448,13 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ambiguity. If <replaceable class="parameter">count</replaceable> is omitted in a <literal>FETCH</literal> clause, it defaults to 1. - <literal>ROW</literal> - and <literal>ROWS</literal> as well as <literal>FIRST</literal> - and <literal>NEXT</literal> are noise words that don't influence - the effects of these clauses. + The <literal>WITH TIES</literal> option is used to return any additional + rows that tie for the last place in the result set according to + <literal>ORDER BY</literal> clause; <literal>ORDER BY</literal> + is mandatory in this case. + <literal>ROW</literal> and <literal>ROWS</literal> as well as + <literal>FIRST</literal> and <literal>NEXT</literal> are noise + words that don't influence the effects of these clauses. According to the standard, the <literal>OFFSET</literal> clause must come before the <literal>FETCH</literal> clause if both are present; but <productname>PostgreSQL</productname> is laxer and allows either order. |