Request to return Pokemon with HP by type

Question

I have a database full of Pokemon cards, and I want to make a request to find the Pokemon with the highest HP for each type. I want the view to display only the name, type and HP of this Pokemon.

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

I have this code. It returns maximum HP, but not the correct Pokemon. Will this code show multiple lines with the same HP for the same 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 the pokemon names with maximum 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 for type pokemon

MaxPerType is the highest level HP list for each type, then you join it with a complete list of all available Pokémon and filter out only those that have the highest HP level for each type.

This will display multiple lines with the same HP for the same type.

By the way, in the mssql server there is a function 'OVER (PARTITION BY' , which is a bit simpler and probably faster. I don’t know if this exists in mysql

Syntax:

 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 

P.Ranking = 1 will provide you only the highest (maximum) HP

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

The advantage is that you do not need to join the table with yourself and that you can select any column that you want to select. Including columns, such as Id, which is not possible in the scripted group. (An identifier in a group means that each record gets its own row as a result, if the identifier is unique, of course)

Answer 3

I have not tested this on mysql, but a sub-net connection should work

 SELECT name from 'Pokemon' as p INNER JOIN (SELECT Max(HP) as maxhp, Type FROM 'Pokemon' GROUP BY TYPE) as maxh on maxhp = p.hp and maxh.Type = p.Type 

This will return the name of each Pokémon in each category with maximum health.