greatest / least issue

[grayashe]

Hi,

 

I have a table of prices that looks something like this:

 

id    price    discount_price

1    10.00    9.50

2    12.50    0.00

3    15.00    12.00

 

I need a way to be able to select products based on price where the discount_price trumps the actual price as long as the discount price is greater than 0.00. I need to do this in the sql.

 

I had thought about using least(price, discount_price), but can't see a way to ignore discount_price when it is zero.

 

Any ideas?

Thanks

[fenway]

Why not store NULL as the discount price if it's "0", as use IFNULL()?

[ajlisowski]

SELECT IF((`price`>`discount_price` AND `discount_price`>0),`discount_price`,`price`) AS `actual_price` FROM `prices`;