From: Daryl Richter <daryl@brandywine.com>
To: pgsql-sql@postgresql.org
Subject: Re: ORDER records based on parameters in IN clause
Date: Thu, 30 Jun 2005 07:58:39 -0400
Message-ID: <42C3DE6F.9080105@brandywine.com> (raw)
In-Reply-To: <42C3A0A3.40101@dltmedia.nl>
References: <BAY103-F2C56F6C371B9B2FDEEDCBB5E00@phx.gbl>
<1120057638.8208.117.camel@state.g2switchworks.com>
<42C3A0A3.40101@dltmedia.nl>
M.D.G. Lange wrote:
> Another option would be:
>
> SELECT * FROM table WHERE id=2003 OR id=1342 OR id=799 OR id=1450;
>
> This should give you the results in the right order...
>
Per the SQL Standard, the rows of a table have no ordering. The result
of a SELECT is just a derived table. Assuming a row order is *always* a
bug.
If you want an explicit row order you *must* use an ORDER BY clause.
I would also recommend to you a saying that I learned many years ago,
"Filter on the server, sort on the client."
> - Michiel
>
> Scott Marlowe wrote:
>
>> On Wed, 2005-06-29 at 09:22, Russell Simpkins wrote:
>>
>>
>>> fair enough. but a simple order by id would never work.
>>>
>>>
>>
>>
>> Try this:
>>
>> select *,
>> case when id=2003 then 1 when id=1342 then 2
>> when id=799 then 3 when id=1450 then 4 end as ob
>> from tablename where id in (2003,1342,799,1450) order by ob;
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>>
>>
>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
--
Daryl Richter
Platform Author & Director of Technology
v: 610.361.1000 x202
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
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: daryl@brandywine.com
Subject: Re: ORDER records based on parameters in IN clause
In-Reply-To: <42C3DE6F.9080105@brandywine.com>
* 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