I have a 'product' table with 3 columns:
priority (1 or 0)
SELECT * FROM product ORDER BY priority DESC, CASE priority = 1 THEN price END ASC, CASE priority = 0 THEN price END DESC
I don't think this is possible just by using order by. One possible solution would be to make two queries, one where priority=0 and another where priority=1 and then use union to join the result sets.
SELECT * FROM product WHERE priority = 1 ORDER BY price ASC UNION ALL SELECT * FROM product WHERE priority = 0 ORDER BY price DESC