如何筛选各族所有最强战力英雄？GROUP BY单最大值问题求助

Got it, let's sort out this issue! Your original query only returns one hero per race because when using GROUP BY race with MAX(Power), SQL picks a single arbitrary hero from each race group—even if multiple heroes share the maximum power. Here are two reliable solutions to get all top-power heroes per race:

Solution 1: Subquery + Join (Works with Most Databases) #

This approach first calculates the maximum power for each race, then matches those values back to the original table to grab all heroes with that power:

SELECT h.race, h.hero
FROM heroes_info h
INNER JOIN (
    -- Get the max power for each race
    SELECT race, MAX(power) AS max_power
    FROM heroes_info
    GROUP BY race
) race_maxes 
    ON h.race = race_maxes.race 
    AND h.power = race_maxes.max_power
ORDER BY h.race;

Solution 2: Window Functions (Cleaner for Modern Databases) #

If your database supports window functions (like MySQL 8+, PostgreSQL, SQL Server, etc.), using RANK() is a more concise way. RANK() keeps all ties for first place, which is exactly what we need:

SELECT race, hero
FROM (
    SELECT 
        race,
        hero,
        -- Rank heroes in each race by power (highest first)
        RANK() OVER (PARTITION BY race ORDER BY power DESC) AS power_rank
    FROM heroes_info
) ranked_heroes
WHERE power_rank = 1
ORDER BY race;

Note: You could also use DENSE_RANK() here—it behaves the same as RANK() for this use case, since we're only filtering for rank 1.

Either of these queries will return your expected output, including all heroes tied for the highest power in their race.

内容的提问来源于stack exchange，提问作者IISsENII