SQLで上位n位(上位n件)のデータを取得する [SQLServer]

SQLServerのT-SQLで上位n件のデータを取得する方法を紹介します。
SQLServerにはランキング上位を取得するために使える関数などが用意されているのでそれを使用します。

上位n件を取得するためのテーブルとデータ

上位n件のデータを取得するために、テーブルを準備します。
テーブルは学生の成績を保持します。
テーブルの列には学生ID、氏名、得点の3つを作成します。
上位ランキングの判定は得点列に対して行います。
テーブルの定義と登録するデータは以下のようになります。

学生成績テーブル(STUDENT_GRADE)列定義

列名 データ型 PK
STUDENT_ID int
STUDENT_NAME nvarchar(20)
SCORE int

学生成績テーブルのCREATE文

学生成績テーブルのデータ

学生ID 氏名 得点
1 学生 氏名 1 391
2 学生 氏名 2 489
3 学生 氏名 3 437
4 学生 氏名 4 339
5 学生 氏名 5 493
6 学生 氏名 6 361
7 学生 氏名 7 470
8 学生 氏名 8 456
9 学生 氏名 9 417
10 学生 氏名 10 391
11 学生 氏名 11 461
12 学生 氏名 12 298
13 学生 氏名 13 443
14 学生 氏名 14 426
15 学生 氏名 15 383

学生成績テーブルのINSERT文

上記のSTUDENT_GRADEテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。

1回のINSERT(インサートSQL)で複数行のレコードを一括挿入(追加)する
SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

ランキング上位n件を取得

SQLServerでは上位ランクを取得する方法として

  • RANK関数(またはDENSE_RANK関数)を使う方法
  • ROW_NUMBER関数を使う方法
  • ORDER BY句とTOP句を使う方法

などがあります。

RANK関数を使って取得する

SQLServerのRANK関数は文字通り、取得するレコードにランクを付けることができる関数です。
RANK関数の構文は以下に示す通りです。

引数のPARTITION BY clause(PARTITION BY句)は取得するレコードをグループ化する際に使用します。
例えば性別を表すGENDERという列に「男性: 1」「女性: 2」というデータが登録されており、男性と女性を分けて順位をつける場合に「PARTITION BY GENDER」と指定します。
PARTITION BY clauseを指定しない場合は、すべてのレコードを1つのグループとして扱います。
引数のORDER BY clause(ORDER BY句)はレコードの順序を決めるのに必要なので、必ず指定します。

以下にRANK関数で学生の成績(得点)に順位をつけて取得するSQLと取得結果の一覧を記載します。

SQL

取得結果

学生の成績をRANK関数で順位をつけて取得

順位 学生ID 氏名 得点
1 5 学生 氏名 5 493
2 2 学生 氏名 2 489
3 7 学生 氏名 7 470
4 11 学生 氏名 11 461
5 8 学生 氏名 8 456
6 13 学生 氏名 13 443
7 3 学生 氏名 3 437
8 14 学生 氏名 14 426
9 9 学生 氏名 9 417
10 10 学生 氏名 10 391
10 1 学生 氏名 1 391
12 15 学生 氏名 15 383
13 6 学生 氏名 6 361
14 4 学生 氏名 4 339
15 12 学生 氏名 12 298

RANK関数は同一の順位に対して同じ番号を付けます。
上記の例では10位の「学生IDが10の学生」と「学生IDが1の学生」が同じ順位になっています。
そして、次の「学生IDが15の学生」の順位が12位になっています。

学生の成績(得点)の順位をつけることができたので、上位n件のレコードを取得します。
ここでは成績が上位10位までの学生を取得します。

SQL

RANK関数で順位を付けて取得するSELECT文をサブクエリにします。(9行目~17行目)
サブクエリにした結果表のSCORE_RANK列に対して10以下のレコードのみにWHERE句で絞るための条件を指定します。(19行目~20行目)

取得結果

学生の成績をRANK関数で上位10件取得

順位 学生ID 氏名 得点
1 5 学生 氏名 5 493
2 2 学生 氏名 2 489
3 7 学生 氏名 7 470
4 11 学生 氏名 11 461
5 8 学生 氏名 8 456
6 13 学生 氏名 13 443
7 3 学生 氏名 3 437
8 14 学生 氏名 14 426
9 9 学生 氏名 9 417
10 10 学生 氏名 10 391
10 1 学生 氏名 1 391

RANK関数とDENSE_RANK関数の違い

RANK関数とDENSE_RANK関数とはほとんど同じですが、取得するデータに同順位がある場合の次の順位の付け方が違います。
RANK関数では10位の「学生IDが10の学生」と「学生IDが1の学生」が同じ順位なので、次の「学生IDが15の学生」の順位が12位になりますが、DENSE_RANK関数では順位が飛ばずに11位になります。

ROW_NUMBER関数を使って取得する

SQLServerのROW_NUMBER関数はRANK関数とは似ていますが少し違います。
RANK関数は同じ順位に対して同じ番号を付けますが、ROW_NUMBER関数はすべての行に対して順番に番号を付けていきます。
ROW_NUMBER関数の構文は以下に示す通りです。

引数のPARTITION BY value_expression(PARTITION BY値式)はRANK関数の引数PARTITION BY clauseと同様に、取得するレコードをグループ化する際に使用します。PARTITION BY clauseを省略した場合は、すべてのレコードを対象に番号を付番します。
引数のORDER BY clause(ORDER BY句)はRANK関数のと同じです。この引数の指定は必須です。

以下にROW_NUMBER関数で学生の成績(得点)に順位をつけて取得するSQLと取得結果の一覧を記載します。

SQL

取得結果

学生の成績をROW_NUMBER関数で順位をつけて取得

順位 学生ID 氏名 得点
1 5 学生 氏名 5 493
2 2 学生 氏名 2 489
3 7 学生 氏名 7 470
4 11 学生 氏名 11 461
5 8 学生 氏名 8 456
6 13 学生 氏名 13 443
7 3 学生 氏名 3 437
8 14 学生 氏名 14 426
9 9 学生 氏名 9 417
10 10 学生 氏名 10 391
11 1 学生 氏名 1 391
12 15 学生 氏名 15 383
13 6 学生 氏名 6 361
14 4 学生 氏名 4 339
15 12 学生 氏名 12 298

ROW_NUMBER関数の場合はすべての行に順位を付けますので、例えば上位10件を取得する場合に1~9位までは1人ずついるが10位が2人以上いた場合に、同順位の10位のいずれか1人の順位が11位になります。
上記の例では、「学生IDが1の学生」が11位になっています。

RANK関数の時と同様に、上位10件のレコードを取得します。

SQL

取得結果

学生の成績をROW_NUMBER関数で上位10件取得

順位 学生ID 氏名 得点
1 5 学生 氏名 5 493
2 2 学生 氏名 2 489
3 7 学生 氏名 7 470
4 11 学生 氏名 11 461
5 8 学生 氏名 8 456
6 13 学生 氏名 13 443
7 3 学生 氏名 3 437
8 14 学生 氏名 14 426
9 9 学生 氏名 9 417
10 10 学生 氏名 10 391

ROW_NUMBER関数で順位をつけて上位10件を取得した場合は「学生IDが1の学生」が対象から漏れています。

TOP句とORDER BY句を使って取得する

SQLServerのにはTOP句があるので、ORDER BY句と組み合わせることで上位n件のレコードを取得することができます。

以下にTOP句とORDER BY句を使って学生の成績(得点)が上位10位までの学生を取得します。

SQL

取得結果

TOP句とORDER BY句で上位10件の学生を取得

順位 学生ID 氏名 得点
1 5 学生 氏名 5 493
2 2 学生 氏名 2 489
3 7 学生 氏名 7 470
4 11 学生 氏名 11 461
5 8 学生 氏名 8 456
6 13 学生 氏名 13 443
7 3 学生 氏名 3 437
8 14 学生 氏名 14 426
9 9 学生 氏名 9 417
10 10 学生 氏名 10 391

取得できるレコードはROW_NUMBER関数を使って取得する場合と同じになります。

今回はシンプルな例をご紹介しましたが、SQLServerにはRANK関数とROW_NUMBER関数という便利な関数が用意されているので、様々な用途で使用することができると思います。以前はRANK関数、ROW_NUMBER関数ともに実装されていなかったので、順位や行番号を付けたデータを取得するためには、自分で付番するSQLを書かなくてはいけませんでした。
バージョンアップとともに便利な関数などが増えていくのは非常に喜ばしいことです。