From: Michael Fuhr <mike@fuhr.org>
To: Russell Simpkins <russellsimpkins@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER records based on parameters in IN clause
Date: Wed, 29 Jun 2005 05:57:23 -0600
Message-ID: <20050629115723.GA31013@winnie.fuhr.org> (raw)
In-Reply-To: <000b01c57c9c$66589170$6401a8c0@simpkinshome1>
References: <937a483c3b3a9809ec978a24b9d9af76@biglumber.com>
<000b01c57c9c$66589170$6401a8c0@simpkinshome1>
On Wed, Jun 29, 2005 at 07:19:22AM -0400, Russell Simpkins wrote:
>
> Order by id will not do what you want, but this should.
> Select * from table where id = 2003;
> Union all
> Select * from table where id = 1342;
> Union all
> Select * from table where id = 799;
> Union all
> Select * from table where id = 1450;
Note that the semicolons should be omitted everywhere except for
at the end of the entire query. Also, although the implementation
might happen to return rows in that order, the documentation states
that it's not guaranteed to:
http://www.postgresql.org/docs/8.0/static/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)."
As the documentation states elsewhere and as the SQL standard says,
without ORDER BY rows are returned in an unspecified order. The
above query works by accident, not by design, so although it might
work today there's no guarantee that it'll work tomorrow.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, russellsimpkins@hotmail.com
Subject: Re: ORDER records based on parameters in IN clause
In-Reply-To: <20050629115723.GA31013@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox