Home How do I SELECT the ROW with Highest Value in a Query with SUM() and JOIN?

# How do I SELECT the ROW with Highest Value in a Query with SUM() and JOIN?

Toleo
1#
Toleo Published in 2018-02-14 18:02:07Z
 My Query Here http://sqlfiddle.com/#!9/8bcf5a/20/0 SELECT i.id , i.mon , i.item , SUM(v.value) value FROM items i LEFT JOIN values v ON v.item_id = i.id WHERE mon = 'Y' GROUP BY i.id ORDER BY v.value DESC;  I get all the rows with mon = Y, But I only want to get the Highest row with mon = x and value = MAX. So instead of getting id mon item value 2 Y C1 17 1 Y C1 11 3 Y C1 5  I want to get id mon item value 2 Y C1 17  I can use LIMIT 1, But the Query would still fetch all the rows, So it is like hiding the problem. Because I don't want The query to fetch 1000 row If i had them with mon = Y, But only the row with mon = Y and value = MAX I've tried something like AND value = MAX(value)  or AND value = TOP(value)  But ofcource it seems incorrect overall
perpetualjourney
2#
 just add a LIMIT clause to your query as follows:  SELECT i.id,i.mon , i.item, SUM(v.value) AS value FROM items i LEFT JOIN values v ON v.item_id = i.id WHERE mon = 'Y' GROUP BY i.id ORDER BY v.value DESC LIMIT 1;  that will only return the first row (note I just added the last line).
 You're grouping the data on the original value on the table instead of the sum of rhe values. Try this:  SELECT i.id , i.mon , i.item , SUM(v.value) totalValue FROM items i LEFT JOIN values v ON v.item_id = i.id WHERE mon = 'Y' GROUP BY i.id, i.mon , i.item ORDER BY SUM( v.value) DESC LIMIT 1; 
 Try this in your clause: AND value in (select MAX(value) from values)  intead of AND value = MAX(value)  and include AND rownum = 1