Question 113632 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Trying to find max value of a sub-set of dat

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search