Postgresql – SQL – Find top rows where parameter is the same

postgresql

I am learning sql and I am jumping into more complicated queries. I have the following tables:

players:
Columns: name, school_name, gender

simple:
Columns: player_name_fkey, school_name_fkey, category, tournament_number, score

My entries in the simple table will be similar to this. Each line is the score by a player at a specific tournament number.

Joe, Harvard, Junior, 1, 40
Joe, Harvard, Junior, 2, 30
Joe, Harvard, Junior, 3, 60
Anne, MIT, Senior, 1, 45
Anne, MIT, Senior, 2, 55
Anne, MIT, Senior, 3, 20 etc.

What I am looking to do is to get, for each player, the sum of his / hers top two scores of the season:

Joe, Harvard, 100
Anne, MIT, 90

I have the following query:

SELECT player_name_fkey,
       school_name_fkey,
       sum(t1.score) AS sum_scores
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         rank() OVER (ORDER BY score DESC) AS scorerank
  FROM simple
    LEFT JOIN players ON players.name = simple.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
  GROUP BY simple.player_name_fkey,
           simple.school_name_fkey,
           simple.score
) t1
WHERE scorerank <= 2
GROUP BY player_name_fkey,
         school_name_fkey

Unfortunately, this query returns only the top 2 scores over all players. How can I change it to get what I need?

Best Answer

You need to calculate the rank per player. You are currently calculating the overall rank.

To do it per player, you need to add a partition by to your window function:

rank() over (PARTITION BY player_name_fkey order by score desc) AS scorerank

You also need to remove the group by from the inner (t1) query:

SELECT player_name_fkey,
       school_name_fkey,
       sum(t1.score) AS sum_scores
FROM (
  SELECT player_name_fkey,
         school_name_fkey,
         score,
         rank() over (PARTITION BY player_name_fkey order by score desc) AS scorerank
  FROM simple
    LEFT JOIN players ON players.name = simple.player_name_fkey
  WHERE category = 'Benjamin'
    AND gender = 'Masculin'
) t1
WHERE scorerank <= 2
GROUP BY player_name_fkey,
         school_name_fkey
Related Question