ROW_NUMBER / RANK / DENSE_RANK

ROW_NUMBER: 重複なしの連番を付与します。

ROW_NUMBER() OVER ([PARTITION BY group_col] ORDER BY sort_col)

RANK: 同順位の次の番号をスキップします。

RANK() OVER ([PARTITION BY group_col] ORDER BY sort_col)

DENSE_RANK: 同順位があっても番号をスキップしません。

DENSE_RANK() OVER ([PARTITION BY group_col] ORDER BY sort_col)

関数	概要
ROW_NUMBER()	同値の行があっても重複しない一意の連番を付与します。
RANK()	同順位の行が複数ある場合、同じ順位を付与し、次の番号をスキップします（1,2,2,4 など）。
DENSE_RANK()	同順位の行が複数あっても次の番号をスキップしません（1,2,2,3 など）。

以下の『members』テーブルを例に説明します。

スコアの降順で3種類の順位を同時に表示します。

sample_row_number_rank.sql

SELECT
    name,
    role,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM members
ORDER BY score DESC;

+------------+--------+-------+---------+----------+----------------+
| name       | role   | score | row_num | rank_val | dense_rank_val |
+------------+--------+-------+---------+----------+----------------+
| 狡噛慎也   | 執行官 |   620 |       1 |        1 |              1 |
| 征陸智己   | 執行官 |   530 |       2 |        2 |              2 |
| 常守朱     | 監視官 |   530 |       3 |        2 |              2 |
| 宜野座伸元 | 監視官 |   420 |       4 |        4 |              3 |
| 唐之杜志恩 | 執行官 |   380 |       5 |        5 |              4 |
+------------+--------+-------+---------+----------+----------------+
5 rows in set

役職ごとにスコア順位を付けて、各役職の上位2名を取得します。

sample_row_number_rank.sql

SELECT name, role, score, role_rank
FROM (
    SELECT
        name,
        role,
        score,
        RANK() OVER (PARTITION BY role ORDER BY score DESC) AS role_rank
    FROM members
) AS m
WHERE role_rank <= 2;

+------------+--------+-------+-----------+
| name       | role   | score | role_rank |
+------------+--------+-------+-----------+
| 狡噛慎也   | 執行官 |   620 |         1 |
| 征陸智己   | 執行官 |   530 |         2 |
| 常守朱     | 監視官 |   530 |         1 |
| 宜野座伸元 | 監視官 |   420 |         2 |
+------------+--------+-------+-----------+
4 rows in set

『ROW_NUMBER()』『RANK()』『DENSE_RANK()』は MySQL（8.0以降）・PostgreSQL・Oracle・SQLite（3.25以降）で共通して使用できます。

SELECT
    name,
    role,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM members
ORDER BY score DESC;

3つの関数の違いは同順位の扱いです。『ROW_NUMBER()』は常に一意の連番を振るため、同じ値でも異なる番号になります。どの行が先になるかはDBの実装依存です。

『RANK()』と『DENSE_RANK()』は同じ値に同じ順位を付けますが、次の番号の扱いが異なります。スポーツの成績表では RANK（2位が2人いたら次は4位）、段位・級の体系では DENSE_RANK（2位が2人いても次は3位）が適しています。

「上位N位を取得する」には派生テーブル（またはCTE）でまず順位を計算し、外側のWHEREで絞り込む方法を使います。ウィンドウ関数の結果はWHEREで直接フィルタできないためです。ウィンドウ関数の基本構文は『OVER / PARTITION BY』を参照してください。