MySql conditional order by

You can also use the following:

SELECT * 
FROM my_table 
WHERE 1  
ORDER BY price=0, price, id;

The part 'price=0' will be 1 for items with zero price, 0 for items with non-zero price. As the default sort order is ASC, non-zero items are now placed first.

The next bit of the order-by clause means that non-zero items are then sorted by price (again ascending). If any items with non-zero price have the same price, they will be further sorted by id, but we don't care about that.

The last part is only there for items where price=0. As all these items do have the same price, the effect is to sort all the zero-priced items by id.

This will do the trick..

 SELECT * 
    FROM my_table 
    WHERE 1  
    ORDER BY 
      CASE price WHEN 0 THEN 1
       ELSE -1
      END ASC, price asc, id asc