Re: ORDER records based on parameters in IN clause

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/