Query to return Pokemon with HP by Type

Question

I have a database full of Pokemon Cards, and I want to do a query to find the pokemon that has the highest HP by each type. I want the view to show just the name, type, and HP of that pokemon.

SELECT MAX(HP), Type, name FROM `Pokemon` group by Type

I have this code. It returns the max HP but not the correct Pokemon. Will this code show multiple rows with the same HP for a single type?

Answer 1

Try this instead:

SELECT p2.MAXHP, p2.Type, p1.name 
FROM `Pokemon` p1
INNER JOIN
(
   SELECT Type, MAX(HP) MaxHP
   FROM Pokemon
   GROUP BY Type
) p2 ON p1.type = p2.type AND p1.HP = p2.MaxHP

This will give you all pokemon names with the max HP.

Answer 2

Select HP, Type, Name 
from `Pokemon` P inner join 
  (Select Type, Max(HP) MaxHP from `Pokemon` group by Type) MaxPerType 
on P.Type = MaxPerType.Type and P.HP = MaxPerType.MaxHP

this will give you a list of all the highest HP per type of Pokemon

MaxPerType is a list of the Highest HP per type, then you join this with the total list of all available Pokemons and filter out only those that have the highest HP per type.

This will show multiple rows with the same HP for a single type.

BTW in mssql server exists the 'OVER (PARTITION BY' functionality, which is a bit easier and probably also faster. Don't know if this exists in mysql

Syntax would be:

Select HP, Type, Name from 
  (Select HP, Type, Name, 
     ISNULL((ROW_NUMBER() OVER (PARTITION BY Type order by HP DESC)),0) As Ranking
   from `Pokemon`) P 
where P.ranking = 1

the P.Ranking = 1 will give you only the highest (max) HP

the Partition by 'Type' has the same effect as the group by

Advantage is that you do not need to join the table with itself and that you can select any column you want in the select. Including columns like an Id, which is not possible in the group by scenario. (Id in the group by means that every record gets its own line in the result, if the Id is unique of course)