select.sgml

PostgreSQL7.4.6 for Linux

    If <literal>DISTINCT</> is specified, all duplicate rows are
    removed from the result set (one row is kept from each group of
    duplicates).  <literal>ALL</> specifies the opposite: all rows are
    kept; that is the default.
   </para>

   <para>
    <literal>DISTINCT ON ( <replaceable
    class="parameter">expression</replaceable> [, ...] )</literal>
    keeps only the first row of each set of rows where the given
    expressions evaluate to equal.  The <literal>DISTINCT ON</literal>
    expressions are interpreted using the same rules as for
    <literal>ORDER BY</> (see above).  Note that the <quote>first
    row</quote> of each set is unpredictable unless <literal>ORDER
    BY</> is used to ensure that the desired row appears first.  For
    example,
<programlisting>
SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;
</programlisting>
    retrieves the most recent weather report for each location.  But
    if we had not used <literal>ORDER BY</> to force descending order
    of time values for each location, we'd have gotten a report from
    an unpredictable time for each location.
   </para>

   <para>
    The <literal>DISTINCT ON</> expression(s) must match the leftmost
    <literal>ORDER BY</> expression(s).  The <literal>ORDER BY</> clause
    will normally contain additional expression(s) that determine the
    desired precedence of rows within each <literal>DISTINCT ON</> group.
   </para>
  </refsect2>

  <refsect2 id="SQL-FOR-UPDATE">
   <title id="sql-for-update-title"><literal>FOR UPDATE</literal> Clause</title>

   <para>
    The <literal>FOR UPDATE</literal> clause has this form:
<synopsis>
FOR UPDATE [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ]
</synopsis>
   </para>

   <para>
    <literal>FOR UPDATE</literal> causes the rows retrieved by the
    <command>SELECT</command> statement to be locked as though for
    update.  This prevents them from being modified or deleted by
    other transactions until the current transaction ends.  That is,
    other transactions that attempt <command>UPDATE</command>,
    <command>DELETE</command>, or <command>SELECT FOR UPDATE</command>
    of these rows will be blocked until the current transaction ends.
    Also, if an <command>UPDATE</command>, <command>DELETE</command>,
    or <command>SELECT FOR UPDATE</command> from another transaction
    has already locked a selected row or rows, <command>SELECT FOR
    UPDATE</command> will wait for the other transaction to complete,
    and will then lock and return the updated row (or no row, if the
    row was deleted).  For further discussion see <xref
    linkend="mvcc">.
   </para>

   <para>
    If specific tables are named in <literal>FOR UPDATE</literal>,
    then only rows coming from those tables are locked; any other
    tables used in the <command>SELECT</command> are simply read as
    usual.
   </para>

   <para>
    <literal>FOR UPDATE</literal> cannot be used in contexts where
    returned rows can't be clearly identified with individual table
    rows; for example it can't be used with aggregation.
   </para>

   <para>
    <literal>FOR UPDATE</literal> may appear before
    <literal>LIMIT</literal> for compatibility with
    <productname>PostgreSQL</productname> versions before 7.3.  It
    effectively executes after <literal>LIMIT</literal>, however, and
    so that is the recommended place to write it.
   </para>
  </refsect2>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To join the table <literal>films</literal> with the table
   <literal>distributors</literal>:

<programlisting>
SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d, films f
    WHERE f.did = d.did

       title       | did |     name     | date_prod  |   kind
-------------------+-----+--------------+------------+----------
 The Third Man     | 101 | British Lion | 1949-12-23 | Drama
 The African Queen | 101 | British Lion | 1951-08-11 | Romantic
 ...
</programlisting>
  </para>

  <para>
   To sum the column <literal>len</literal> of all films and group
   the results by <literal>kind</literal>:

<programlisting>
SELECT kind, sum(len) AS total FROM films GROUP BY kind;

   kind   | total
----------+-------
 Action   | 07:34
 Comedy   | 02:58
 Drama    | 14:28
 Musical  | 06:42
 Romantic | 04:38
</programlisting>
  </para>

  <para>
   To sum the column <literal>len</literal> of all films, group
   the results by <literal>kind</literal> and show those group totals
   that are less than 5 hours:

<programlisting>
SELECT kind, sum(len) AS total
    FROM films
    GROUP BY kind
    HAVING sum(len) < interval '5 hours';

   kind   | total
----------+-------
 Comedy   | 02:58
 Romantic | 04:38
</programlisting>
  </para>

  <para>
   The following two examples are identical ways of sorting the individual
   results according to the contents of the second column
   (<literal>name</literal>):

<programlisting>
SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;

 did |       name
-----+------------------
 109 | 20th Century Fox
 110 | Bavaria Atelier
 101 | British Lion
 107 | Columbia
 102 | Jean Luc Godard
 113 | Luso films
 104 | Mosfilm
 103 | Paramount
 106 | Toho
 105 | United Artists
 111 | Walt Disney
 112 | Warner Bros.
 108 | Westward
</programlisting>
  </para>

  <para>
   The next example shows how to obtain the union of the tables
   <literal>distributors</literal> and
   <literal>actors</literal>, restricting the results to those that begin
   with the letter W in each table.  Only distinct rows are wanted, so the
   key word <literal>ALL</literal> is omitted.

<programlisting>
distributors:               actors:
 did |     name              id |     name
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributors.name
    FROM distributors
    WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM actors
    WHERE actors.name LIKE 'W%';

      name
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen
</programlisting>
  </para>

  <para>
   This example shows how to use a function in the <literal>FROM</>
   clause, both with and without a column definition list:

<programlisting>
CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS '
    SELECT * FROM distributors WHERE did = $1;
' LANGUAGE SQL;

SELECT * FROM distributors(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS '
    SELECT * FROM distributors WHERE did = $1;
' LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney
</programlisting>
  </para>
 </refsect1>
 
 <refsect1>
  <title>Compatibility</title>

  <para>
   Of course, the <command>SELECT</command> statement is compatible
   with the SQL standard.  But there are some extensions and some
   missing features.
  </para>
  
  <refsect2>
   <title>Omitted <literal>FROM</literal> Clauses</title>

   <para>
    <productname>PostgreSQL</productname> allows one to omit the
    <literal>FROM</literal> clause.  It has a straightforward use to
    compute the results of simple expressions:
<programlisting>
SELECT 2+2;

 ?column?
----------
        4
</programlisting>
    Some other <acronym>SQL</acronym> databases cannot do this except
    by introducing a dummy one-row table from which to do the
    <command>SELECT</command>.
   </para>

   <para>
    A less obvious use is to abbreviate a normal
    <command>SELECT</command> from tables:
<programlisting>
SELECT distributors.* WHERE distributors.name = 'Westward';

 did |   name
-----+----------
 108 | Westward
</programlisting>
    This works because an implicit <literal>FROM</literal> item is
    added for each table that is referenced in other parts of the
    <command>SELECT</command> statement but not mentioned in
    <literal>FROM</literal>.
   </para>

   <para>
    While this is a convenient shorthand, it's easy to misuse.  For
    example, the command
<programlisting>
SELECT distributors.* FROM distributors d;
</programlisting>
    is probably a mistake; most likely the user meant
<programlisting>
SELECT d.* FROM distributors d;
</programlisting>
    rather than the unconstrained join
<programlisting>
SELECT distributors.* FROM distributors d, distributors distributors;
</programlisting>
    that he will actually get.  To help detect this sort of mistake,
    <productname>PostgreSQL</productname> will warn if the
    implicit-<literal>FROM</literal> feature is used in a
    <command>SELECT</command> statement that also contains an explicit
    <literal>FROM</literal> clause.  Also, it is possible to disable
    the implicit-<literal>FROM</literal> feature by setting the
    <varname>ADD_MISSING_FROM</> parameter to false.
   </para>
  </refsect2>

  <refsect2>
   <title>The <literal>AS</literal> Key Word</title>

   <para>
    In the SQL standard, the optional key word <literal>AS</> is just
    noise and can be omitted without affecting the meaning.  The
    <productname>PostgreSQL</productname> parser requires this key
    word when renaming output columns because the type extensibility
    features lead to parsing ambiguities without it.
    <literal>AS</literal> is optional in <literal>FROM</literal>
    items, however.
   </para>
  </refsect2>

  <refsect2>
   <title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>

   <para>
    In the SQL92 standard, an <literal>ORDER BY</literal> clause may
    only use result column names or numbers, while a <literal>GROUP
    BY</literal> clause may only use expressions based on input column
    names.  <productname>PostgreSQL</productname> extends each of
    these clauses to allow the other choice as well (but it uses the
    standard's interpretation if there is ambiguity).
    <productname>PostgreSQL</productname> also allows both clauses to
    specify arbitrary expressions.  Note that names appearing in an
    expression will always be taken as input-column names, not as
    result-column names.
   </para>

   <para>
    SQL99 uses a slightly different definition which is not entirely upward
    compatible 
    with SQL92.  In most cases, however, <productname>PostgreSQL</productname>
    will interpret an <literal>ORDER BY</literal> or <literal>GROUP
    BY</literal> expression the same way SQL99 does.
   </para>
  </refsect2>

  <refsect2>
   <title>Nonstandard Clauses</title>

   <para>
    The clauses <literal>DISTINCT ON</literal>,
    <literal>LIMIT</literal>, and <literal>OFFSET</literal> are not
    defined in the SQL standard.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->