Query to return Pokemon with highest attack by group

Question

I have a database full of Pokemon Cards, and their attacks. I want to do a query to find the Pokemon that has the strongest attack by each type. I want the view to show just the name, type, and damage of the attack.

SELECT p2.MaxD, p2.Type, p1.name 
FROM Pokemon p1 
INNER JOIN ( SELECT type, MAX(damage) MaxD, pokemon_name FROM Attack GROUP BY Type )
p2 ON p1.type = p2.type AND p2.pokemon_name = p1.name

I have this code. It returns the highest damage but not the correct Pokemon. The Pokemon table doesn't have a damage field. I'm trying to get a grasp of joins.

Here is the structure:

Attack table has 4 fields: pokemon_name (the pokemon this attack belongs to), damage, name (name of the attack), and type (the type of pokemon this attack belongs to).

The Pokemon table has 3: HP, type (of the pokemon), and name (of the pokemon).

Answer 1

First of all you have to build select that select maximal damage for each type (you already have that):

SELECT type, MAX(damage) MaxD FROM Attack GROUP BY Type

Now, this won't have a good performance unless:

• type is INT (or ENUM or other numeric type)
• there's index on type or type, damage

You cannot select pokemon_name because MySQL doesn't guarantee that you'll get pokemon_name matching MaxD (here's a nice answer on stackoverflow which already covers this issue).

Now you can select pokemon with that matching pokemon_name

SELECT p1.pokemon_name, p1.type, p1.damage
FROM Attack p1
INNER JOIN (
    SELECT type, MAX(damage) MaxD FROM Attack GROUP BY Type
) p2 ON p1.type = p2.type
     AND p1.damage = p2.MaxDamage
GROUP BY (p1.type, p1.damage)

The last GROUP BY statement makes sure that having multiple pokemons with the same attack damage won't cause multiple records for one type,damage pairs.

Again, you will achieve good performance by replacing pokemon_name with pokemon_id. Maybe you should google database normalization for a while [wikipedia],[first tutorial]. You also may want to check this Q&A out, it provides nice overview of what does "relation table" mean.

Now you have correct pokemon_name (for your program sake, I hope you'll replace this with pokemon_id) and you may put it all together:

SELECT p1.pokemon_name, p1.type, p1.damage, p.*
FROM Attack p1
INNER JOIN (
    SELECT type, MAX(damage) MaxD FROM Attack GROUP BY Type
) p2 ON p1.type = p2.type
     AND p1.damage = p2.MaxDamage
INNER JOIN Pokemon p
     ON  p.pokemon_name = p1.pokemon_name
GROUP BY (p1.type, p1.damage)

---

Ideal example

In perfect world you're database would look like this:

-- Table with pokemons
CREATE TABLE `pokemons` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255),
    -- More fields
    PRIMARY KEY (`id`)
)

-- This contains pairs as (1,'Wather'), (2, 'Flame'), ...
CREATE TABLE `AttackTypes` (
    `id`,
    `name` VARCHAR(255)
)

-- Create records like (1, 2, 3, 152)
-- 1 = automatically generated keys
-- 2 = id of pokemon (let say it's Pikachu :P)
-- 3 = type of attack (this say it's Electric)
-- 152 = damage
-- This way each pokemon may have multiple attack types (Charizard flame + wind)
CREATE TABLE `Attacks` (
    `id`,
    `pokemonID` INT NOT NULL, -- Represents pokemons.id
    `typeID` INT NOT NULL, -- Represents attack.id
    `damage` INT
)

ID fields are ALWAYS PRIMARY KEY, NOT NULL and AUTO_INCREMENT in this example

And the select from it, again get types first:

SELECT MAX(attack.damage) AS mDmg, attack.typeID
FROM attack
GROUP BY attack.typeID

Than get pokemon ID:

SELECT a.pokemonID, a.damage, a.typeID
FROM attack AS a
INNER JOIN (
    SELECT MAX(a.damage) AS mDmg, a.typeID
    FROM attack AS a
    GROUP BY a.typeID
) AS maxA
    ON a.typeID = maxA.typeID
        AND a.damage = mDmg
GROUP BY (a.typeID)

And once you've covered all that you may actually select pokemon data

SELECT aMax.pokemonID as id,
    aMax.damage, 
    p.name AS pokemonName,
    aMax.typeID AS attackTypeID,
    t.name AS attackType

FROM (
    SELECT a.pokemonID, a.damage, a.type
    FROM attack AS a
    INNER JOIN (
        SELECT MAX(a.damage) AS mDmg, a.type
        FROM attack AS a
        GROUP BY a.type
    ) AS maxA
        ON a.type = maxA.type
            AND a.damage = mDmg
    GROUP BY (a.type)
) AS aMax

INNER JOIN pokemons AS p
    ON p.id = aMax.pokemonID

INNER JOIN AttackTypes AS t
    ON t.id = aMax.typeID

Performance hints:

• you may add field MaxDamage into AttackTypes (which would be calculated by stored procedure) and will save you one level of nasted query
• all ID fields should be PRIMARY KEYs
• index on Attacks.typeID allows you to quickly get all pokemons capable of that type of attack
• index on Attack.damage allows you to quickly find strongest attack
• index on Attack.type, Attack.damage (two fields) will be helpful when finding max value for each attack
• index on Attack.pokemonID will make look up pokemon -> attack -> attack type name faster

Answer 2

I'm not really sure about your schema but I assumed that the pokemon_name of your attack table is really the name of the pokemon.

SELECT  a.*, c.*
FROM    Attack a
        INNER JOIN
        (
            SELECT  type, MAX(damage) MaxD
            FROM Attack 
            GROUP BY Type 
        ) b ON a.Type = b.Type AND
                a.damage = b.MaxD
        INNER JOIN Pokemon c
            ON c.Name = a.pokemon_name AND
                c.Type = a.Type

the above query displays all field from attack table and pokemon table, but If you are really interested on the name, damage and type the you only do query on attack table

SELECT  a.*
FROM    Attack a
        INNER JOIN
        (
            SELECT  type, MAX(damage) MaxD
            FROM Attack 
            GROUP BY Type 
        ) b ON a.Type = b.Type AND
                a.damage = b.MaxD