Issue

This Content is from Stack Overflow. Question asked by lemonzi

I have written the following query to query a databse:

SELECT `year` AS "Year",
    country_Name AS "Country",
    avg_mon_temp AS "Max Temperature"
FROM world_temps
LEFT JOIN countries
ON world_temps.country_Code = countries.country_Code
#GROUP BY `year`, country_Name

This returns approx 5000 rows of data in the following format:
Data Set

How can return the MAXIMUM temperature per year. For instance, I only want the highest temperature for 1901, then the highest for 1902, and so on.

Solution

Seems you just need to use GROUP BY AND MAX to do it

SELECT `t.year` AS "Year",
    c.country_Name AS "Country",
    MAX(t.avg_mon_temp) AS "Max Temperature"
FROM world_temps t
LEFT JOIN countries c
ON t.country_Code = c.country_Code
GROUP BY `t.year`, c.country_Name

This Question was asked in StackOverflow by lemonzi and Answered by lucumt It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.