CODEWITHSUNDEEP
mysql
Gremlin
Gremlin

Reputation: 29

MYSQL select max value if quantity is greater

I have a table with the following data:

product_id         qty       price_reduction
     1               1              5
     1               2              10
     1               3              15

How can I get maximum price reduction (-15) if qty is greater than 3. For now I use this query, which doesn't do what I want it to do for obvious reasons:

SELECT price_reduction 
  FROM table
 WHERE product_id = 1
   AND qty = '$qty' 
 LIMIT 1

Any help appreciated.

Upvotes: 0

Views: 306

Answers (1)

fubar
fubar

Reputation: 17398

If you're just trying to get a single result, then filtering the results to only those <= :qty, ordering by qty DESC, and limiting the result to one should work.

SELECT price_reduction
FROM table
WHERE product_id = 1
AND qty <= :qty
ORDER BY qty DESC
LIMIT 1

Here's a working SQL Fiddle.

Upvotes: 1

Related Questions