Select by greatest sum, but without the sum in the result

Question

I need to select the top score of all combined attempts by a player and I need to use a WITH clause.

create table scorecard(
 id integer primary key,
 player_name varchar(20));

create table scores(
 id integer references scorecard,
 attempt integer,
 score numeric
 primary key(id, attempt));

Sample Data for scorecard:

id       player_name
1        Bob      
2        Steve    
3        Joe    
4        Rob

Sample data for scores:

id    attempt    score
1        1         50
1        2         45
2        1         10
2        2         20
3        1         40
3        2         35
4        1         0
4        2         95

The results would simply look like this:

player_name
Bob
Rob

But would only be Bob if Rob had scored less than 95 total. I've gotten so far as to have the name and the total scores that they got in two columns using this:

select scorecard.player_name, sum(scores.score)
from scorecard
left join scores
on scorecard.id= scores.id
group by scorecard.name
order by sum(scores.score) desc;

But how do I just get the names of the highest score (or scores if tied).

And remember, it should be using a WITH clause.

Create 2 queries: one that selects the max sum of scores. The other calculates scores per player. Put them both in `WITH` and you're done. — zerkms, Oct 21 '15 at 03:58

score 1 · Answer 1 · edited May 23 '17 at 11:52

Who ever told you to "use a WITH clause" was missing a more efficient solution. To just get the (possibly multiple) winners:

SELECT c.player_name
FROM   scorecard c
JOIN  (
   SELECT id, rank() OVER (ORDER BY sum(score) DESC) AS rnk
   FROM   scores
   GROUP  BY 1
   ) s USING (id)
WHERE  s.rnk = 1;

A plain subquery is typically faster than a CTE. If you must use a WITH clause:

WITH top_score AS (
   SELECT id, rank() OVER (ORDER BY sum(score) DESC) AS rnk
   FROM   scores
   GROUP  BY 1
   )
SELECT c.player_name
FROM   scorecard c
JOIN   top_score s USING (id)
WHERE  s.rnk = 1;

SQL Fiddle.

You could add a final ORDER BY c.player_name to get a stable sort order, but that's not requested.

The key feature of the query is that you can run a window function like rank() over the result of an aggregate function. Related:

score 0 · Answer 2 · answered Oct 21 '15 at 04:04

Can try something like follows.

With (SELECT id, sum(score) as sum_scores
      FROM scores
      group by id) as sumScoresTable,

With (SELECT max(score) as max_scores
      FROM scores
      group by id) as maxScoresTable

select player_name 
FROM scorecard
WHERE scorecard.id in (SELECT sumScoresTable.id 
                       from sumScoresTable 
                       where  sumScoresTable.score = (select maxScoresTable.score from maxScoresTable)

score 0 · Answer 3 · answered Oct 21 '15 at 04:43

Try this code:

   WITH CTE AS (
       SELECT ID, RANK() OVER(ORDER BY SumScore DESC) As R
       FROM (
          SELECT ID, SUM(score) AS SumScore
          FROM scores
          GROUP BY ID )
    )

   SELECT player_name
   FROM scorecard
   WHERE ID IN (SELECT ID FROM CTE WHERE R = 1)

Select by greatest sum, but without the sum in the result

3 Answers3