Trying to find max value of a sub-set of dat-CodePudding

I have written the following query to query a database:

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.

CodePudding user response：

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

CodePudding user response：

you can try for:

SELECT t1.year AS "Year"
      ,t2.country_Name AS "Country"
      ,MAX(t.avg_mon_temp) AS "Max_Temperature"
  FROM world_temps t1
LEFT JOIN countries t2
ON t1.country_Code = t2.country_Code
GROUP BY t1.year, t2.country_Name