下面是一个可能的解决方案：

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;

请参阅fiddle。

如果出现并列，则需要rank（）来保持排名。如果不需要在排名之间设置间隔，则需要dense_rank（）。

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;

结果（包括rank（）和dense_rank（）以供参考）。

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