Find records with MAX value in each

Question

I'm using mysql. And here is one table, which I made for example:

http://sqlfiddle.com/#!2/0ca23/1

In this table I've got fields: Country, City, Resource, Volume, other

I need to SELECT records which contains MAX value of Volume field of each Resource each City each Country. I've tried these query:

    SELECT `Country`, `City`, `Resource`, MAX(`Volume`), `other`
    FROM `temp`
    GROUP BY `Country`, `City`, `Resource`

but data was messed up (at field 'other').

To be clear that's what I'm trying to achieve.

http://sqlfiddle.com/#!2/0ad62a/1

I need the WHOLE record, which contains MAX Volume value.

I've already read SQL Select only rows with Max Value on a Column and know that there is a INNER JOIN - way to solve that but don't get, how to do it with multiple grouping. Thank you for reading.

score 5 · Accepted Answer · answered Dec 13 '13 at 12:48

Check this updated Fiddle of yours: http://sqlfiddle.com/#!2/0ca23/4

SELECT temp.*
FROM temp
JOIN 
   (SELECT `Country`, `City`, `Resource`, MAX(`Volume`) AS MaxVol
   FROM `temp`
   GROUP BY `Country`, `City`, `Resource`) t
   ON temp.country = t.country 
   AND temp.city = t.city 
   AND temp.resource = t.resource 
   AND temp.volume = t.MaxVol

This query is basically making a INNER JOIN of your main table with the subquery which gets the max(volume) records for each country, city, and resource. The subquery results are aliased as table t.

Thabk you! I'll check it on my actual table and let you know the result — NikVankevsky, Dec 13 '13 at 14:21

score 2 · Answer 2 · answered Dec 13 '13 at 12:47

2

Since you're not grouping on the other column, MySQL will give you a random value from within the group. In fact, other RDBMS such as SQL Server does not even allow you to SELECT a column on which you don't have an aggregate function or a group by.

So the solution, in your case, depends on what you want to return for the other column. Do you want just the value that belongs to the group that has the maximum volume? In that case, do something like this:

SELECT `Country`, `City`, `Resource`, `Volume`, `other`
FROM `temp` t1
WHERE `Volume` = (SELECT MAX(`Volume`) FROM `temp` t2 WHERE t1.`Country` = t2.`Country`
                     AND t1.`City` = t2.`City` AND t1.`Resource` = t2.`Resource`)

answered Dec 13 '13 at 12:47

Dan

10,480
23
49

Note that this solution is likely to be slower than other, documented methods. – Strawberry Dec 13 '13 at 12:48
Thank you! It seems working too. I'll check it on my work table – NikVankevsky Dec 13 '13 at 14:31
Again, thanks a lot for help! Your variant is also working, but takes a bit longer, and that's important for me, because I've got a quite big table (about 55 000 000 records) – NikVankevsky Dec 14 '13 at 04:57

score 0 · Answer 3 · answered Dec 13 '13 at 13:37

And one more way in the collection using MySQL feature: User-Defined Variables.

select Country, City, Resource, Volume,other 
from 
(
SELECT Country, City, Resource, Volume,other,
if(@c1<>Country OR @c2<>City OR @c3<>Resource,1,0) 
   as FlagField,
@c1:=Country,
@c2:=City,
@c3:=Resource
FROM temp, (Select @c1:='',@c2:='',@c3:='') as t1
ORDER BY Country, City, Resource,Volume DESC
) t2 
WHERE FlagField=1

SQLFiddle demo

This query also solves the issue when there are more than one record with Value=MAX(Value) per group. If you use a way with JOIN a subquery or WHERE Volume=(..MAX(Value)..) then you also should handle this issue with (for example) one more Group BY in the main query to leave only one record per group. For instance (@Aziz Shaikh query):

SELECT temp.Country,temp.City,temp.Value,MAX(other)
FROM temp
JOIN 
   (SELECT `Country`, `City`, `Resource`, MAX(`Volume`) AS MaxVol
   FROM `temp`
   GROUP BY `Country`, `City`, `Resource`) t
   ON temp.country = t.country 
   AND temp.city = t.city 
   AND temp.resource = t.resource 
   AND temp.volume = t.MaxVol
GROUP BY temp.Country,temp.City,temp.Value

Oh, thank you, but that seems too complicated for me. I need only one maxValue — NikVankevsky, Dec 13 '13 at 14:22

Find records with MAX value in each

3 Answers3