sorting on two price fields in MySQL

Sorting on two price fields in MySQL was a confusing thought at first, and was tempted to query then use php to do it. No!

I needed a way to select my products and order them by price. But we have two prices, a “price” and “price_offer”, so we need to compare these values so we can get our results sorted just how we need them…

[code language=”sql”]
$q = "SELECT id FROM product_variations
WHERE product_id = ‘$product_id’
AND active = ‘1’
ORDER BY
(
CASE
WHEN price_offer < price
THEN price
ELSE price_offer
END
)
ASC";[/code]
easy when you know how!




No Comments


You can leave the first : )



Leave a Reply

Your email address will not be published. Required fields are marked *