CODEWITHSUNDEEP
mysql
JTC
JTC

Reputation: 3464

mysql ORDER BY IF ELSE

I was thinking if there is some more advanced way to use ORDER BY in mysql query.

I have query to get products from db, using order by price. The problem is if discount value not null, it overrides the main price, and it should somehow override the ORDER BY line.

IDEA:

DESC

product_price discount
900           0
800           0
1200          700 //dicount overrides price
600           0



The query got 25 lines, but the logic is something like this:

SELECT
product_price as price,
IFNULL(discount_amount,0) as discount

FROM not_relevant

WHERE not_relevant

ORDER BY product_price DESC

So when i write products to category sort by price, certain products have discount so the main price is overwritten and sort only by price is not accurate.

I tried to use coalesce, or two ORDER BY.

Any ideas? Thanks for help.

Upvotes: 2

Views: 11825

Answers (3)

Zagor23
Zagor23

Reputation: 1963

I think this should work for you:

SELECT
product_price,
IFNULL(discount_amount,0) as discount,
IFNULL(discount_amount,product_price) as price

FROM not_relevant

WHERE not_relevant

ORDER BY price DESC

It basically does what you're asking... If discount is not null it will be used, else it uses product_price, and orders the results.

Upvotes: 3

gvee
gvee

Reputation: 17161

SELECT product_price As price
     , Coalesce(discount_amount, 0) As discount
     , Coalesce(discount_amount, product_price) As order_by_this
FROM   not_relevant
WHERE  not_relevant
ORDER
    BY order_by_this

Alternatively:

SELECT product_price As price
     , Coalesce(discount_amount, 0) As discount
FROM   not_relevant
WHERE  not_relevant
ORDER
    BY Coalesce(discount_amount, product_price)

Upvotes: 1

skv
skv

Reputation: 1803

This can be used if discount is actually discounted price and not discount amount 

SELECT product_price as price,
IFNULL(discount_amount,0) as discount

FROM not_relevant
    ORDER BY 
CASE WHEN discount_amount IS NULL THEN price ELSE discount_amount END

Upvotes: 1

Related Questions