File: queries-order.html

package info (click to toggle)
postgresql-9.1 9.1.15-0%2Bdeb8u1
links: PTS, VCS
area: main
in suites: jessie-kfreebsd
size: 109,092 kB
sloc: ansic: 575,877; sql: 43,887; yacc: 26,399; perl: 6,352; lex: 6,171; sh: 5,282; makefile: 3,772; asm: 65; sed: 15; python: 12
file content (415 lines) | stat: -rw-r--r-- 7,369 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Sorting Rows</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.15 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Queries"
HREF="queries.html"><LINK
REL="PREVIOUS"
TITLE="Combining Queries"
HREF="queries-union.html"><LINK
REL="NEXT"
TITLE="LIMIT and OFFSET"
HREF="queries-limit.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2015-02-02T21:03:01"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.15 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Combining Queries"
HREF="queries-union.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="queries.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 7. Queries</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="LIMIT and OFFSET"
HREF="queries-limit.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="QUERIES-ORDER"
>7.5. Sorting Rows</A
></H1
><P
>   After a query has produced an output table (after the select list
   has been processed) it can optionally be sorted.  If sorting is not
   chosen, the rows will be returned in an unspecified order.  The actual
   order in that case will depend on the scan and join plan types and
   the order on disk, but it must not be relied on.  A particular
   output ordering can only be guaranteed if the sort step is explicitly
   chosen.
  </P
><P
>   The <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause specifies the sort order:
</P><PRE
CLASS="SYNOPSIS"
>SELECT <TT
CLASS="REPLACEABLE"
><I
>select_list</I
></TT
>
    FROM <TT
CLASS="REPLACEABLE"
><I
>table_expression</I
></TT
>
    ORDER BY <TT
CLASS="REPLACEABLE"
><I
>sort_expression1</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>ASC | DESC</SPAN
>] [<SPAN
CLASS="OPTIONAL"
>NULLS { FIRST | LAST }</SPAN
>]
             [<SPAN
CLASS="OPTIONAL"
>, <TT
CLASS="REPLACEABLE"
><I
>sort_expression2</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>ASC | DESC</SPAN
>] [<SPAN
CLASS="OPTIONAL"
>NULLS { FIRST | LAST }</SPAN
>] ...</SPAN
>]</PRE
><P>
   The sort expression(s) can be any expression that would be valid in the
   query's select list.  An example is:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT a, b FROM table1 ORDER BY a + b, c;</PRE
><P>
   When more than one expression is specified,
   the later values are used to sort rows that are equal according to the
   earlier values.  Each expression can be followed by an optional
   <TT
CLASS="LITERAL"
>ASC</TT
> or <TT
CLASS="LITERAL"
>DESC</TT
> keyword to set the sort direction to
   ascending or descending.  <TT
CLASS="LITERAL"
>ASC</TT
> order is the default.
   Ascending order puts smaller values first, where
   <SPAN
CLASS="QUOTE"
>"smaller"</SPAN
> is defined in terms of the
   <TT
CLASS="LITERAL"
>&lt;</TT
> operator.  Similarly, descending order is
   determined with the <TT
CLASS="LITERAL"
>&gt;</TT
> operator.
    <A
NAME="AEN4170"
HREF="#FTN.AEN4170"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
>
  </P
><P
>   The <TT
CLASS="LITERAL"
>NULLS FIRST</TT
> and <TT
CLASS="LITERAL"
>NULLS LAST</TT
> options can be
   used to determine whether nulls appear before or after non-null values
   in the sort ordering.  By default, null values sort as if larger than any
   non-null value; that is, <TT
CLASS="LITERAL"
>NULLS FIRST</TT
> is the default for
   <TT
CLASS="LITERAL"
>DESC</TT
> order, and <TT
CLASS="LITERAL"
>NULLS LAST</TT
> otherwise.
  </P
><P
>   Note that the ordering options are considered independently for each
   sort column.  For example <TT
CLASS="LITERAL"
>ORDER BY x, y DESC</TT
> means
   <TT
CLASS="LITERAL"
>ORDER BY x ASC, y DESC</TT
>, which is not the same as
   <TT
CLASS="LITERAL"
>ORDER BY x DESC, y DESC</TT
>.
  </P
><P
>   A <TT
CLASS="REPLACEABLE"
><I
>sort_expression</I
></TT
> can also be the column label or number
   of an output column, as in:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;</PRE
><P>
   both of which sort by the first output column.  Note that an output
   column name has to stand alone, that is, it cannot be used in an expression
   &mdash; for example, this is <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>not</I
></SPAN
> correct:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- wrong</PRE
><P>
   This restriction is made to reduce ambiguity.  There is still
   ambiguity if an <TT
CLASS="LITERAL"
>ORDER BY</TT
> item is a simple name that
   could match either an output column name or a column from the table
   expression.  The output column is used in such cases.  This would
   only cause confusion if you use <TT
CLASS="LITERAL"
>AS</TT
> to rename an output
   column to match some other table column's name.
  </P
><P
>   <TT
CLASS="LITERAL"
>ORDER BY</TT
> can be applied to the result of a
   <TT
CLASS="LITERAL"
>UNION</TT
>, <TT
CLASS="LITERAL"
>INTERSECT</TT
>, or <TT
CLASS="LITERAL"
>EXCEPT</TT
>
   combination, but in this case it is only permitted to sort by
   output column names or numbers, not by expressions.
  </P
></DIV
><H3
CLASS="FOOTNOTES"
>Notes</H3
><TABLE
BORDER="0"
CLASS="FOOTNOTES"
WIDTH="100%"
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN4170"
HREF="queries-order.html#AEN4170"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>      Actually, <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> uses the <I
CLASS="FIRSTTERM"
>default B-tree
      operator class</I
> for the expression's data type to determine the sort
      ordering for <TT
CLASS="LITERAL"
>ASC</TT
> and <TT
CLASS="LITERAL"
>DESC</TT
>.  Conventionally,
      data types will be set up so that the <TT
CLASS="LITERAL"
>&lt;</TT
> and
      <TT
CLASS="LITERAL"
>&gt;</TT
> operators correspond to this sort ordering,
      but a user-defined data type's designer could choose to do something
      different.
     </P
></TD
></TR
></TABLE
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="queries-union.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="queries-limit.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Combining Queries</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="queries.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><TT
CLASS="LITERAL"
>LIMIT</TT
> and <TT
CLASS="LITERAL"
>OFFSET</TT
></TD
></TR
></TABLE
></DIV
></BODY
></HTML
>