Date: Wed, 29 Jun 2005 05:57:23 -0600
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
Message-ID: <20050629115723.GA31013@winnie.fuhr.org>
References: <937a483c3b3a9809ec978a24b9d9af76@biglumber.com>
	<000b01c57c9c$66589170$6401a8c0@simpkinshome1>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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/