Message-ID: <BAY103-F2C56F6C371B9B2FDEEDCBB5E00@phx.gbl>
In-Reply-To: <20050629115723.GA31013@winnie.fuhr.org>
From: "Russell Simpkins" <russellsimpkins@hotmail.com>
To: mike@fuhr.org
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER records based on parameters in IN clause
Date: Wed, 29 Jun 2005 10:22:07 -0400
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

fair enough. but a simple order by id would never work.

>From: Michael Fuhr <mike@fuhr.org>
>To: Russell Simpkins <russellsimpkins@hotmail.com>
>CC: pgsql-sql@postgresql.org
>Subject: Re: [SQL] ORDER records based on parameters in IN clause
>Date: Wed, 29 Jun 2005 05:57:23 -0600
>
>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/
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)