ROW_NUMBER, RANK, or DENSE_RANK to have the SAME order number if there are rows with same values-CodePudding

PROBLEM Form a scoreboard with position number, player name and best score. If two players have the same score, they share the position and the names are in alpabetical order. (See example.)

I have two tables

INSERT INTO players (id,name) VALUES (1, Uolevi),(2,Maija),(3,Liisa),(4,Kaaleppi),(5,Kotivalo);

INSERT INTO results (id, player_id, score) VALUES (1, 1, 100), (2, 2, 200), (3, 3, 200), (4, 4, 100), (5, 5, 50);

The expected result is:

Order	Name	Score
1	Liisa	200
1	Maija	200
3	Kaaleppi	100
3	Uolevi	100
5	Kotivalo	50

Please look carefully at the order number. Because there are 2 rows with order number 1, the next order number will be 3, instead of 2.

CodePudding user response：

Here is a possible solution:

WITH
    places AS (
        SELECT row_number() OVER (ORDER BY score DESC, name) AS place, name, score
        FROM players
        JOIN results ON players.id = results.player_id
    )
SELECT first_value(place) OVER (PARTITION BY score ORDER BY place) AS place, name, score
FROM places ORDER BY score DESC, name;

See the fiddle.

CodePudding user response：

You need rank() to keep ranking if there is a tie. dense_rank() when you don't need gaps between ranks.

select rank() over (order by r.score desc)       as order1,
       dense_rank() over (order by r.score desc) as order2,
       p.name,
       r.score
  from players p
  join results r
    on p.id = r.player_id
 order by r.score desc, p.name;

Outcome (include both rank() and dense_rank() for your reference).

| order1 | order2 | name      | score |
 -------- -------- ----------- ------- 
| 1      | 1      | Liisa     |   200 |
| 1      | 1      | Maija     |   200 |
| 3      | 2      | Kaaleppi  |   100 |
| 3      | 2      | Uolevi    |   100 |
| 5      | 3      | Kotivalo  |    50 |