CODEWITHSUNDEEP
pythonsqlitecountwindow-functionsdense-rank
HJA24
HJA24

Reputation: 361

Create ranking within set of rows resulting from GROUP BY

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

Upvotes: 1

Views: 66

Answers (1)

forpas
forpas

Reputation: 164139

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.

Upvotes: 1

Related Questions