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

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#
perpetualjourney Reply to 2018-02-14 18:06:13Z

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).

cdaiga
3#
cdaiga Reply to 2018-02-14 18:24:12Z

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;
Lucas Thiago Batista
4#
Lucas Thiago Batista Reply to 2018-02-14 18:29:23Z

Try this in your clause:

AND value in (select MAX(value) from values)

intead of

AND value = MAX(value)

and include

AND rownum = 1
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.509471 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO