1

I am joining 3 tables with this query

SELECT DISTINCT a.number, c.quantity, c.retail_price 
FROM catalog.product `a`
JOIN catalog.product_variation `b` ON a.id = b.product_id
JOIN catalog.price_regular `c` ON b.id = c.product_variation_id
WHERE c.retail_price BETWEEN 5 AND 6 AND a.status_id = 1
ORDER BY a.number, c.retail_price DESC

and I get this result set

number|quantity|retail_price
---------------------
1007  | 288    | 5.750
1007  | 48     | 5.510
1007  | 576    | 5.460
1007  | 96     | 5.240
1007  | 576    | 5.230
1007  | 144    | 5.120
1006  | 200    | 5.760
1006  | 100    | 5.550
1006  | 200    | 5.040
1006  | 500    | 5.010

What I need is the results to only contain the row with the greatest value in the quantity column and also the row with the greatest retail_price. So my result set I need would look like this

number|quantity|retail_price
---------------------
1006  | 500    | 5.010
1007  | 576    | 5.460

I found a few posts on SO but none were helpful when joining multiple tables. I need a sql statement to get the result set specified above

Community
  • 1
  • 1
Angelo
  • 407
  • 8
  • 18
  • There are actually 2 rows with number 1007 amd quantity 576. How do you know which one to choose? – Lamak Mar 16 '15 at 15:01
  • That's true. I would need to choose the one with the highest retail_price as well. I've modified the question – Angelo Mar 16 '15 at 15:02
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Mar 16 '15 at 15:20

1 Answers1

-1

This is a simple GROUP BY query

SELECT a.number, max(c.quantity) as qty, max(c.retail_price) as price 
FROM catalog.product `a` 
JOIN catalog.product_variation `b` ON a.id = b.product_id 
JOIN catalog.price_regular `c` ON b.id = c.product_variation_id 
WHERE c.retail_price BETWEEN 5 AND 6 
AND a.status_id = 1 
GROUP BY a.number;
georgecj11
  • 1,600
  • 15
  • 22
  • It is a simple query, but it's not this one. At least, not if the OP's desired result set is anything to go by. – Strawberry Mar 16 '15 at 16:01