Create ranking within set of rows resulting from GROUP BY-CodePudding

I have the following table

CREATE TABLE "results" (
    "player"    INTEGER,
    "tournament"    INTEGER,
    "year"  INTEGER,
    "course"    INTEGER,
    "round" INTEGER,
    "score" INTEGER,
);

With the following data sample for a single tournament / year / round-combination.

1  33  2016  895  1  20  
2  33  2016  895  1  10
3  33  2016  895  1  25
4  33  2016  895  1  28
7  33  2016  895  1  25
8  33  2016  895  1  17
9  33  2016  895  1  12

I would like to create a new column called ranking that represents the ranking of the player for that particular tournament / year / round-combination. The player with the most points is #1. If players score the same, they are tied which needs to specified with a "T".

The desired output looks as follows:

1  33  2016  895  1  20  3
2  33  2016  895  1  12  T5 
3  33  2016  895  1  25  T2
4  33  2016  895  1  28  1 
7  33  2016  895  1  25  T2
8  33  2016  895  1  17  4
9  33  2016  895  1  12  T5

How can I achieve the above? Thanks

CodePudding user response：

Use DENSE_RANK() window function to do the ranking and COUNT() window function check if it is needed to concatenate 'T' at the start:

SELECT *,
       CASE WHEN COUNT(*) OVER (PARTITION BY tournament, year, course, round, score) > 1 THEN 'T' ELSE '' END ||
       DENSE_RANK() OVER (PARTITION BY tournament, year, course, round ORDER BY score DESC) AS ranking
FROM results
ORDER BY player;

See the demo.

If course is not important for this ranking then remove it from both PARTITION BY clauses.

CodePudding user response：

You can use the following SQL query to add the ranking column to the table and populate it with the desired values:

WITH scores AS (
  SELECT player, tournament, year, course, round, score,
         ROW_NUMBER() OVER (PARTITION BY tournament, year, round ORDER BY score DESC) as ranking
  FROM results
)
SELECT player, tournament, year, course, round, score,
       CASE 
         WHEN COUNT(CASE WHEN score = MAX(score) OVER (PARTITION BY tournament, year, round) THEN 1 END) > 1 THEN 'T'
         ELSE ''
       END || ranking as ranking
FROM scores
ORDER BY tournament, year, round, ranking;

The query uses a Common Table Expression (CTE) called "scores" to first add a ranking column to the table based on the score column, with the highest score getting a ranking of 1. Then it selects the player, tournament, year, course, round, and score columns along with the new ranking column. The ranking column is constructed by first checking if there are any ties (i.e., if there are more than one player with the maximum score) and if so, it concatenates a "T" with the ranking value. Finally, the query orders the output by tournament, year, round, and ranking.