Philip Philip - 1 year ago 56
MySQL Question

Different order depending on condition

I have a 'product' table with 3 columns:

id (int)
priority (1 or 0)
price (int)

After I've ordered the products by priority desc, I would like to order the two subsets by price. the subset with priority = 1 by ascending price and those with priority = 0 by descending price.
I've tried things like this:

SELECT * FROM product ORDER BY priority DESC, CASE priority = 1 THEN price END ASC, CASE priority = 0 THEN price END DESC

but nothing I tried seemed to work.
Any idea on how to approach this?

Answer Source

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
SELECT * FROM product WHERE priority = 0 ORDER BY price DESC