MySQL Order By Help

[LuckyB]

Hi Guys

I want to order by a Featured List of product ids - which is passed from PHP. So firstly it orders by the featured list, then it falls back and orders by price. I have tried the following, but it seems to pull the featured ones last, not first.

...ORDER BY ( CASE p2.product_id WHEN 4 THEN 0 WHEN 33 THEN 1 WHEN 22 THEN 2 WHEN 20 THEN 3 WHEN 16 THEN 4 WHEN 27 THEN 5 END ) ASC, pa1.price ASC

So ideally the order would be:
1) product_id = 4 (if it is found by the query)
2) product_id = 33 (if it is found by the query)
3) product_id = 22 (if it is found by the query)
4) product_id = 20 (if it is found by the query)
4) product_id = 16 (if it is found by the query)
5) product_id = 27 (if it is found by the query)
6) Everything else that is found by the query, in price ASC order.

Hope that makes sense.

Thanks

[guido2004]

Add a ELSE 9999 to the CASE:

ORDER BY 
    (CASE p2.product_id 
       WHEN 4 THEN 0 
       WHEN 33 THEN 1 
       WHEN 22 THEN 2 
       WHEN 20 THEN 3 
       WHEN 16 THEN 4 
       WHEN 27 THEN 5 
       ELSE 9999
    END ) ASC
  , pa1.price ASC

[LuckyB]

I also tried the following with no love:

ORDER BY FIELD(p2.product_id,4,33,22,20,16,27) ASC, pa1.price ASC

[LuckyB]

Add a ELSE 9999 to the CASE:

ORDER BY 
    (CASE p2.product_id 
       WHEN 4 THEN 0 
       WHEN 33 THEN 1 
       WHEN 22 THEN 2 
       WHEN 20 THEN 3 
       WHEN 16 THEN 4 
       WHEN 27 THEN 5 
       ELSE 9999
    END ) ASC
  , pa1.price ASC

Thanks much from Australia!