Re: order by question

[Sergio Gabriel Rodriguez <sgrodriguez@xxxxxxxxx>]

On Fri, Jun 19, 2009 at 10:46 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:

On Fri, Jun 19, 2009 at 7:32 PM, Tony Liao<tonyliao@xxxxxxxxxxxxx> wrote:
> Thanks,All,
>          To Tino,I want to order by IN condition "(6,3,4,10,7)".
>          To Sergio,my answer is "Yes".
>          To Kenny,I think this is not the result I want to get.
>          To Scott,In database,there are thousands of  products.the select
> query is generated by application,and it is not sure how many product_id
> in condition,maybe the next query would be .........product_id in
> (4,26,7,8,9,23,27,54)

If you generate the product_id in  (4,26,7,8,9,23,27,54) part then
it's pretty easy to create the order by case part too.

I agree with Scott, if your application generate IN condition, could create CASE too, looks like this

SELECT id, product_id FROM your_table 
   WHERE product_id IN (6, 3, 4, 10, 7) 
    ORDER BY 
      CASE 
        WHEN product_id = 6  THEN 1 
        WHEN product_id = 3  THEN 2 
        WHEN product_id = 4  THEN 3
        WHEN product_id = 10 THEN 4
        WHEN product_id = 7  THEN 5
      END;

Regards,

Sergio Gabriel Rodriguez
http://www.3trex.com.ar