# sql - Selecting highest value from with a grouped by query

I have a query:

``select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total fromdq.datagroup by points,location order by location,total desc``

Which produces this data:

``FRANCE |0|2|0|0|0|0|1 110.0FRANCE |0|2|1|0|1|2|1 100.0FRANCE |0|2|0|0|0|1|1 100.0FRANCE |0|2|1|0|0|1|1 100.0FRANCE |0|2|0|1|1|2|1 100.0FRANCE |0|2|0|0|1|1|1 100.0GERMANY |1|0|2|2|2|1|0 120.0GERMANY |1|0|2|2|2|0|0 110.0GERMANY |1|0|2|2|2|2|0 110.0GERMANY |1|0|2|2|2|0|2 110.0GERMANY |1|0|2|2|2|1|1 110.0``

I want to get to highest `total` and the related `points` for each `location`.

I should end up with:

``FRANCE |0|2|0|0|0|0|1 110.0GERMANY |1|0|2|2|2|1|0 120.0``

I believe I need to use a subquery and `MAX(total)`, but I cant get this to work.

In the subquery, I want to select `points`, but I dont want to group by it which is obviously not allowed.

How do I go about doing this?

Your instincts are correct. You can do this by calculating the max total and then joining this back to the original data:

``````select t.*
from (select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
from dq.data
group by points,location
) t join
(select location, max(total) as maxtotal
from (select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
from dq.data
group by points,location
) t
group by location
) tsum
on t.location = tsum.location and t.total = tsum.maxtotal
``````

Note that this version will return duplicates if there are ties at the top.

I am not intimately familiar with google-biggquery. If it supports the "with" statement, then you can simplify the query, by doing:

``````with t as (select substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
from dq.data
group by points,location
)
select t.*
from t join
(select location, max(total) as maxtotal
from t
group by location
) tsum
on t.location = tsum.location and t.total = tsum.maxtotal
``````

If it supports windows functions (such as row_number()), then you can eliminate the explicit join altogether.

I had a similar problem recently, solved it similar to this:

``````SELECT substr(name,7,50) as location, points,sum(if (p1=r1,10,-10))as total
FROM (
SELECT * FROM dq.data ORDER BY location,sum(if (p1=r1,10,-10)) desc
) tmp
GROUP BY points,location;
``````

Not sure if it will work as my db was MySQL, but it's a nice intuitive solution. Order the subquery the way you want the summary rows to fall out.