CODEWITHSUNDEEP
mysqldatabasemax
Santiago Troitiño Cristancho
Santiago Troitiño Cristancho

Reputation: 54

How can I "tell" MySQL to return the maximum value for each category and some other value associated with that max value?

Here I have my table called "Pais"(Country in Spanish), Id_Pais is the Primary Key and Continente_FK(Continente stands for Continent) is a foreign key that relates each country to a continent, below you can see my data

What I'm trying to do is a table that shows me by continent the maximum extension of a country AND the name of the country that has the max extension, I have done the first part already with these code: 

SELECT MAX(Extension) FROM Pais GROUP BY Continente_FK;

That code shows me the max extension associated with each continent, I tried this other code, but it only shows me the max extension out of all countries, not the max for each continent(the max out of all):

SELECT C.Id_Continente, P.Nombre, P.Extension,C.Nombre
FROM Pais P RIGHT OUTER JOIN Continente C ON C.Id_Continente=P.Continente_FK 
WHERE P.Extension=(SELECT MAX(Extension) FROM Pais)
GROUP BY C.Id_Continente;

"Nombre" means "Name"

I really don't know how to tell MySQL to "give" the name that has associated the max extension, please help.

Thanks!

Upvotes: 0

Views: 45

Answers (1)

Jon C
Jon C

Reputation: 664

You're missing a condition in the subquery:

SELECT C.Id_Continente, P.Nombre, P.Extension,C.Nombre
FROM Pais P RIGHT OUTER JOIN Continente C 
ON C.Id_Continente=P.Continente_FK 
WHERE P.Extension=(SELECT MAX(Extension) FROM Pais P2
                   WHERE P2.Continente_FK = C.Id_Continente)
GROUP BY C.Id_Continente;

The only problem I see is if you have two countries from the same continent with same extension (which is unlikely). You'll always get one result per continent.

Upvotes: 1

Related Questions