I have a table like these

This is a table in the database. It shows 70 countries around the world with player names and gender. It shows which player score how many goals in which year. The years goes from 1990 to 2015. So the table is large. Now I would like to know which female player and which male player score most in every year from 2010 to 2012.

I expect this:

I used that code but got an error

SELECT gender,year,player, max(score) as score from (football) where player = max(score) and year in ('2010','2011','2012') group by 1,2,3

football is the table name

CodePudding user response：

with main as (
select
gender,
player,
year,
sum(score) as total_score -- incase each player played multiple match in a year
from <table_name>
where year between 2010 and 2012
group by 1,2,3
), 
ranking as (
select *,
row_number(total_score) over(partition by year, gender order by total_score desc) as rank_ 
)

select 
gender,
player,
year,
total_score
from ranking where rank_ = 1

• filter on years
• first you add total score, to make sure you cover the cases if there are multiple matches played by the same player in same year
• then you create a rank based on year, gender and the total score, so for a given year and for a given gender create a rank
• then you filter on rank_ = 1 as it represents the highest score

CodePudding user response：

You can use the dense_rank function to achieve this, if you are using sqlite version 3.25 or higher.

Query

select t.* from(
    select *, dense_rank() over(
      partition by year, gender
      order by score desc
   ) as rn
   from football
   where year in ('2010','2011','2012')
) as t
where t.rn = 1;