Re: How to force select to return exactly one row

Brett Mc Bride <brett.mcbride@deakin.edu.au> wrote:

> My understanding of UNION ALL is that it won't sort the rows...?
> [...]

It doesn't, but that's not promised for every data set, ev-
ery PostgreSQL version, every phase of the moon. To quote
<URI:http://www.postgresql.org/docs/8.4/interactive/queries-union.html>:

| UNION effectively appends the result of query2 to the result
| of query1 (although there is no guarantee that this is the
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| order in which the rows are actually returned). Furthermore,
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
| it eliminates duplicate rows from its result, in the same
| way as DISTINCT, unless UNION ALL is used.

SQL deals with (unordered) sets, and therefore any use of
"LIMIT" without "ORDER BY" indicates a bug waiting to bite
you when you least expect it.

Tim