MySQL Order By Help

Databases
1

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)
  5. product_id = 16 (if it is found by the query)
  6. product_id = 27 (if it is found by the query)
  7. Everything else that is found by the query, in price ASC order.

Hope that makes sense.

Thanks

2

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 
       [B][COLOR="Red"]ELSE 9999[/COLOR][/B]
    END ) ASC
  , pa1.price ASC
3

I also tried the following with no love:

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

4

Thanks much from Australia!