MySQL IF Help!

[angst]

Hello,

I'm running this query to order a product list by price,


SELECT * FROM Products p, DepartmentProducts dp, Departments d, Brands b WHERE p.ProductsID = dp.ProductsID
AND p.BrandID = 237
AND p.Active = 1
AND b.BrandsID = p.BrandID
AND p.GroupedProductsID = 0
AND dp.DepartmentsID = d.DepartmentsID
AND d.DepartmentsID = '12'
GROUP BY p.ProductsID
ORDER BY p.ProductPrice ASC LIMIT 0,18


however some products are on sale, so I would like to order by the sale price instead of the normal price. so I'm trying to do something like this:


SELECT * FROM Products p, DepartmentProducts dp, Departments d, Brands b WHERE p.ProductsID = dp.ProductsID
AND p.BrandID = 237
AND p.Active = 1
AND b.BrandsID = p.BrandID
AND p.GroupedProductsID = 0
AND dp.DepartmentsID = d.DepartmentsID
AND d.DepartmentsID = '12'
GROUP BY p.ProductsID
ORDER BY

IF p.OnSale = 1 THEN
p.ProductPrice
ELSE
p.SalePrice
END IF

ASC LIMIT 0,18


but that doesn't work, any ideas how I can make this run?

[angst]

ok, this seems to work:


CASE WHEN p.OnSale = 1 THEN
p.ProductPrice
ELSE
p.SalePrice
END


if anyone knows of a better way, please let me know.

thanks!

[angst]

ok, my last try didn't return any errors, but also didn't give me the correct result. so any ideas would be a big help.

thanks again.

[angst]

ok, never mind I got it. I messed up and have the ProductPrice and SalePrice reversed. it's working correctly now.