Re: ORDER records based on parameters in IN clause

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          ))