0

Question : Name the most powerful heroes of each race.

Table:

Hero Race Power
a-bomb human 7000
amazo human 7000
ajax cyborg 5000
mephis cyborg 5600
ammo human 1000
ardina alien 2000
jagua alien 200
myntria alien 2300
hyspace alien 2300
...
...
...

and so on.

Now for this data, for the race "human", we have a-bomb and amazo both of whom have the highest power. So my output should include both their names.

My expected output:

Race Hero
human a-bomb
human amazo
cyborg memphis
alien myntria
alien hyspace

Please help.

I tried grouping by race and selecting the max, but that just gives one maximum when the same race has multiple maximum.

SELECT race,hero, max(Power)
FROM heroes_info
GROUP BY race;

My expected output:

Race Hero
human a-bomb
human amazo
cyborg memphis
alien myntria
alien hyspace

My actual output:

Race Hero
human a-bomb
cyborg memphis
alien myntria
Fahmi
  • 37,315
  • 5
  • 22
  • 31
IISsENII
  • 3
  • 4
  • 1
    Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Nick Sep 29 '19 at 05:34

2 Answers2

0

You can try below -

SELECT race,hero, Power
FROM heroes_info a
where power in (select max(pawer) from heroes_info a1 where a.race=a1.race)
Fahmi
  • 37,315
  • 5
  • 22
  • 31
0

we can use the rank function to sort the heroes based on power desc and print the heros with rank 1 as below 

SELECT race, 
       hero 
FROM   (SELECT hero, 
               race, 
               Rank() 
                 OVER ( 
                   partition BY race 
                   ORDER BY power DESC) rank1 
        FROM   heroes_info) AS heroes 
WHERE  heroes.rank1 = 1
Test12345
  • 1,625
  • 1
  • 12
  • 21