I386LINUX/util/I386LINUX/doc/postgresql/html/queries-order.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
   2 <HTML
   3 ><HEAD
   4 ><TITLE
   5 >Sorting Rows</TITLE
   6 ><META
   7 NAME="GENERATOR"
   8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
   9 REV="MADE"
  10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
  11 REL="HOME"
  12 TITLE="PostgreSQL 7.4.1 Documentation"
  13 HREF="index.html"><LINK
  14 REL="UP"
  15 TITLE="Queries"
  16 HREF="queries.html"><LINK
  17 REL="PREVIOUS"
  18 TITLE="Combining Queries"
  19 HREF="queries-union.html"><LINK
  20 REL="NEXT"
  21 TITLE="LIMIT and OFFSET"
  22 HREF="queries-limit.html"><LINK
  23 REL="STYLESHEET"
  24 TYPE="text/css"
  25 HREF="stylesheet.css"><META
  26 NAME="creation"
  27 CONTENT="2003-12-22T03:48:47"></HEAD
  28 ><BODY
  29 CLASS="SECT1"
  30 ><DIV
  31 CLASS="NAVHEADER"
  32 ><TABLE
  33 SUMMARY="Header navigation table"
  34 WIDTH="100%"
  35 BORDER="0"
  36 CELLPADDING="0"
  37 CELLSPACING="0"
  38 ><TR
  39 ><TH
  40 COLSPAN="5"
  41 ALIGN="center"
  42 VALIGN="bottom"
  43 >PostgreSQL 7.4.1 Documentation</TH
  44 ></TR
  45 ><TR
  46 ><TD
  47 WIDTH="10%"
  48 ALIGN="left"
  49 VALIGN="top"
  50 ><A
  51 HREF="queries-union.html"
  52 ACCESSKEY="P"
  53 >Prev</A
  54 ></TD
  55 ><TD
  56 WIDTH="10%"
  57 ALIGN="left"
  58 VALIGN="top"
  59 ><A
  60 HREF="queries.html"
  61 >Fast Backward</A
  62 ></TD
  63 ><TD
  64 WIDTH="60%"
  65 ALIGN="center"
  66 VALIGN="bottom"
  67 >Chapter 7. Queries</TD
  68 ><TD
  69 WIDTH="10%"
  70 ALIGN="right"
  71 VALIGN="top"
  72 ><A
  73 HREF="queries.html"
  74 >Fast Forward</A
  75 ></TD
  76 ><TD
  77 WIDTH="10%"
  78 ALIGN="right"
  79 VALIGN="top"
  80 ><A
  81 HREF="queries-limit.html"
  82 ACCESSKEY="N"
  83 >Next</A
  84 ></TD
  85 ></TR
  86 ></TABLE
  87 ><HR
  88 ALIGN="LEFT"
  89 WIDTH="100%"></DIV
  90 ><DIV
  91 CLASS="SECT1"
  92 ><H1
  93 CLASS="SECT1"
  94 ><A
  95 NAME="QUERIES-ORDER"
  96 >7.5. Sorting Rows</A
  97 ></H1
  98 ><A
  99 NAME="AEN3068"
 100 ></A
 101 ><A
 102 NAME="AEN3070"
 103 ></A
 104 ><P
 105 >   After a query has produced an output table (after the select list
 106    has been processed) it can optionally be sorted.  If sorting is not
 107    chosen, the rows will be returned in random order.  The actual
 108    order in that case will depend on the scan and join plan types and
 109    the order on disk, but it must not be relied on.  A particular
 110    output ordering can only be guaranteed if the sort step is explicitly
 111    chosen.
 112   </P
 113 ><P
 114 >   The <TT
 115 CLASS="LITERAL"
 116 >ORDER BY</TT
 117 > clause specifies the sort order:
 118 </P><PRE
 119 CLASS="SYNOPSIS"
 120 >SELECT <VAR
 121 CLASS="REPLACEABLE"
 122 >select_list</VAR
 123 >
 124     FROM <VAR
 125 CLASS="REPLACEABLE"
 126 >table_expression</VAR
 127 >
 128     ORDER BY <VAR
 129 CLASS="REPLACEABLE"
 130 >column1</VAR
 131 > [<SPAN
 132 CLASS="OPTIONAL"
 133 >ASC | DESC</SPAN
 134 >] [<SPAN
 135 CLASS="OPTIONAL"
 136 >, <VAR
 137 CLASS="REPLACEABLE"
 138 >column2</VAR
 139 > [<SPAN
 140 CLASS="OPTIONAL"
 141 >ASC | DESC</SPAN
 142 >] ...</SPAN
 143 >]</PRE
 144 ><P>
 145    <VAR
 146 CLASS="REPLACEABLE"
 147 >column1</VAR
 148 >, etc., refer to select list
 149    columns.  These can be either the output name of a column (see
 150    <A
 151 HREF="queries-select-lists.html#QUERIES-COLUMN-LABELS"
 152 >Section 7.3.2</A
 153 >) or the number of a column.  Some
 154    examples:
 155 </P><PRE
 156 CLASS="PROGRAMLISTING"
 157 >SELECT a, b FROM table1 ORDER BY a;
 158 SELECT a + b AS sum, c FROM table1 ORDER BY sum;
 159 SELECT a, sum(b) FROM table1 GROUP BY a ORDER BY 1;</PRE
 160 ><P>
 161   </P
 162 ><P
 163 >   As an extension to the SQL standard, <SPAN
 164 CLASS="PRODUCTNAME"
 165 >PostgreSQL</SPAN
 166 > also allows ordering
 167    by arbitrary expressions:
 168 </P><PRE
 169 CLASS="PROGRAMLISTING"
 170 >SELECT a, b FROM table1 ORDER BY a + b;</PRE
 171 ><P>
 172    References to column names in the <TT
 173 CLASS="LITERAL"
 174 >FROM</TT
 175 > clause that are
 176    renamed in the select list are also allowed:
 177 </P><PRE
 178 CLASS="PROGRAMLISTING"
 179 >SELECT a AS b FROM table1 ORDER BY a;</PRE
 180 ><P>
 181    But these extensions do not work in queries involving
 182    <TT
 183 CLASS="LITERAL"
 184 >UNION</TT
 185 >, <TT
 186 CLASS="LITERAL"
 187 >INTERSECT</TT
 188 >, or <TT
 189 CLASS="LITERAL"
 190 >EXCEPT</TT
 191 >,
 192    and are not portable to other SQL databases.
 193   </P
 194 ><P
 195 >   Each column specification may be followed by an optional
 196    <TT
 197 CLASS="LITERAL"
 198 >ASC</TT
 199 > or <TT
 200 CLASS="LITERAL"
 201 >DESC</TT
 202 > to set the sort direction to
 203    ascending or descending.  <TT
 204 CLASS="LITERAL"
 205 >ASC</TT
 206 > order is the default.
 207    Ascending order puts smaller values first, where
 208    <SPAN
 209 CLASS="QUOTE"
 210 >"smaller"</SPAN
 211 > is defined in terms of the
 212    <TT
 213 CLASS="LITERAL"
 214 >&lt;</TT
 215 > operator.  Similarly, descending order is
 216    determined with the <TT
 217 CLASS="LITERAL"
 218 >&gt;</TT
 219 > operator.
 220     <A
 221 NAME="AEN3101"
 222 HREF="#FTN.AEN3101"
 223 ><SPAN
 224 CLASS="footnote"
 225 >[1]</SPAN
 226 ></A
 227 >
 228   </P
 229 ><P
 230 >   If more than one sort column is specified, the later entries are
 231    used to sort rows that are equal under the order imposed by the
 232    earlier sort columns.
 233   </P
 234 ></DIV
 235 ><H3
 236 CLASS="FOOTNOTES"
 237 >Notes</H3
 238 ><TABLE
 239 BORDER="0"
 240 CLASS="FOOTNOTES"
 241 WIDTH="100%"
 242 ><TR
 243 ><TD
 244 ALIGN="LEFT"
 245 VALIGN="TOP"
 246 WIDTH="5%"
 247 ><A
 248 NAME="FTN.AEN3101"
 249 HREF="queries-order.html#AEN3101"
 250 ><SPAN
 251 CLASS="footnote"
 252 >[1]</SPAN
 253 ></A
 254 ></TD
 255 ><TD
 256 ALIGN="LEFT"
 257 VALIGN="TOP"
 258 WIDTH="95%"
 259 ><P
 260 >      Actually, <SPAN
 261 CLASS="PRODUCTNAME"
 262 >PostgreSQL</SPAN
 263 > uses the <I
 264 CLASS="FIRSTTERM"
 265 >default B-tree
 266       operator class</I
 267 > for the column's data type to determine the sort
 268       ordering for <TT
 269 CLASS="LITERAL"
 270 >ASC</TT
 271 > and <TT
 272 CLASS="LITERAL"
 273 >DESC</TT
 274 >.  Conventionally,
 275       data types will be set up so that the <TT
 276 CLASS="LITERAL"
 277 >&lt;</TT
 278 > and
 279       <TT
 280 CLASS="LITERAL"
 281 >&gt;</TT
 282 > operators correspond to this sort ordering,
 283       but a user-defined data type's designer could choose to do something
 284       different.
 285      </P
 286 ></TD
 287 ></TR
 288 ></TABLE
 289 ><DIV
 290 CLASS="NAVFOOTER"
 291 ><HR
 292 ALIGN="LEFT"
 293 WIDTH="100%"><TABLE
 294 SUMMARY="Footer navigation table"
 295 WIDTH="100%"
 296 BORDER="0"
 297 CELLPADDING="0"
 298 CELLSPACING="0"
 299 ><TR
 300 ><TD
 301 WIDTH="33%"
 302 ALIGN="left"
 303 VALIGN="top"
 304 ><A
 305 HREF="queries-union.html"
 306 ACCESSKEY="P"
 307 >Prev</A
 308 ></TD
 309 ><TD
 310 WIDTH="34%"
 311 ALIGN="center"
 312 VALIGN="top"
 313 ><A
 314 HREF="index.html"
 315 ACCESSKEY="H"
 316 >Home</A
 317 ></TD
 318 ><TD
 319 WIDTH="33%"
 320 ALIGN="right"
 321 VALIGN="top"
 322 ><A
 323 HREF="queries-limit.html"
 324 ACCESSKEY="N"
 325 >Next</A
 326 ></TD
 327 ></TR
 328 ><TR
 329 ><TD
 330 WIDTH="33%"
 331 ALIGN="left"
 332 VALIGN="top"
 333 >Combining Queries</TD
 334 ><TD
 335 WIDTH="34%"
 336 ALIGN="center"
 337 VALIGN="top"
 338 ><A
 339 HREF="queries.html"
 340 ACCESSKEY="U"
 341 >Up</A
 342 ></TD
 343 ><TD
 344 WIDTH="33%"
 345 ALIGN="right"
 346 VALIGN="top"
 347 ><TT
 348 CLASS="LITERAL"
 349 >LIMIT</TT
 350 > and <TT
 351 CLASS="LITERAL"
 352 >OFFSET</TT
 353 ></TD
 354 ></TR
 355 ></TABLE
 356 ></DIV
 357 ></BODY
 358 ></HTML
 359 >