diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/queries.sgml | 54 | ||||
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 9 |
2 files changed, 60 insertions, 3 deletions
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index bc0b3cc9fe1..834b83b5098 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1372,6 +1372,55 @@ GROUP BY GROUPING SETS ( </programlisting> </para> + <para> + <indexterm zone="queries-grouping-sets"> + <primary>ALL</primary> + <secondary>GROUP BY ALL</secondary> + </indexterm> + <indexterm zone="queries-grouping-sets"> + <primary>DISTINCT</primary> + <secondary>GROUP BY DISTINCT</secondary> + </indexterm> + When specifying multiple grouping items together, the final set of grouping + sets might contain duplicates. For example: +<programlisting> +GROUP BY ROLLUP (a, b), ROLLUP (a, c) +</programlisting> + is equivalent to +<programlisting> +GROUP BY GROUPING SETS ( + (a, b, c), + (a, b), + (a, b), + (a, c), + (a), + (a), + (a, c), + (a), + () +) +</programlisting> + If these duplicates are undesirable, they can be removed using the + <literal>DISTINCT</literal> clause directly on the <literal>GROUP BY</literal>. + Therefore: +<programlisting> +GROUP BY <emphasis>DISTINCT</emphasis> ROLLUP (a, b), ROLLUP (a, c) +</programlisting> + is equivalent to +<programlisting> +GROUP BY GROUPING SETS ( + (a, b, c), + (a, b), + (a, c), + (a), + () +) +</programlisting> + This is not the same as using <literal>SELECT DISTINCT</literal> because the output + rows may still contain duplicates. If any of the ungrouped columns contains NULL, + it will be indistinguishable from the NULL used when that same column is grouped. + </para> + <note> <para> The construct <literal>(a, b)</literal> is normally recognized in expressions as @@ -1561,7 +1610,12 @@ SELECT a "from", b + c AS sum FROM ... <title><literal>DISTINCT</literal></title> <indexterm zone="queries-distinct"> + <primary>ALL</primary> + <secondary>SELECT ALL</secondary> + </indexterm> + <indexterm zone="queries-distinct"> <primary>DISTINCT</primary> + <secondary>SELECT DISTINCT</secondary> </indexterm> <indexterm zone="queries-distinct"> diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index ab911055994..9c5cf50ef0c 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -37,7 +37,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac [ * | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ] [ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ] [ WHERE <replaceable class="parameter">condition</replaceable> ] - [ GROUP BY <replaceable class="parameter">grouping_element</replaceable> [, ...] ] + [ GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] ] [ HAVING <replaceable class="parameter">condition</replaceable> ] [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] <replaceable class="parameter">select</replaceable> ] @@ -778,7 +778,7 @@ WHERE <replaceable class="parameter">condition</replaceable> <para> The optional <literal>GROUP BY</literal> clause has the general form <synopsis> -GROUP BY <replaceable class="parameter">grouping_element</replaceable> [, ...] +GROUP BY [ ALL | DISTINCT ] <replaceable class="parameter">grouping_element</replaceable> [, ...] </synopsis> </para> @@ -802,7 +802,10 @@ GROUP BY <replaceable class="parameter">grouping_element</replaceable> [, ...] independent <replaceable>grouping sets</replaceable>. The effect of this is equivalent to constructing a <literal>UNION ALL</literal> between subqueries with the individual grouping sets as their - <literal>GROUP BY</literal> clauses. For further details on the handling + <literal>GROUP BY</literal> clauses. The optional <literal>DISTINCT</literal> + clause removes duplicate sets before processing; it does <emphasis>not</emphasis> + transform the <literal>UNION ALL</literal> into a <literal>UNION DISTINCT</literal>. + For further details on the handling of grouping sets see <xref linkend="queries-grouping-sets"/>. </para> |