CODEWITHSUNDEEP
mysqlsqlgroup-bymaxgreatest-n-per-group
NikVankevsky
NikVankevsky

Reputation: 39

Find records with MAX value in each

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.

Upvotes: 1

Views: 2030

Answers (3)

valex
valex

Reputation: 24144

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

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16544

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.

Upvotes: 5

Dan
Dan

Reputation: 10700

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

Upvotes: 2

Related Questions