SQLで行番号を取得する ROW_NUMBER・ROWNUM・ユーザ定義変数の使い方

SQLで行番号

SQLの結果セット（検索結果）に対して、１〜の連番となる行番号を振りたいシーンは多々あるでしょう。

Oracle、MySQL、PostgreSQLなど、ほとんどのRDB（リレーショナルデータベース）には、行番号を振る機能があります。

この記事では、SQLの結果セット（検索結果）に対して行番号を振る方法を解説します。

ROW_NUMBER関数

ROW_NUMBER関数はSELECT文で取得した検索結果に対し、行番号（連番）を振るための関数です。グループ毎に連番を振ったり、指定したソート条件の順に連番を振ったりできます。

ROW_NUMBERの構文

ROW_NUMBER関数の構文を確認しましょう。

ROW_NUMBER() OVER ( [ PARTITION BY [パーティションカラム], [パーティションカラム], ...] ORDER BY [ソートカラム], [ソートカラム], ... )

パーティションカラムは、結果をセットをパーティションカラム毎ごとに１～からの連番を振ります。パーティションカラムを省略した場合、結果セット全体で連番が振られます。

ソートカラムは、連番を振る順序を決めるソートカラムを指定します。ROW_NUMBER関数では、ソートカラムの指定は必須です。
パーティションカラムとソートカラムを同時に指定すると、パーティション毎に、指定したソートカラムの昇順または降順で連番が振られます。

以下は、ROW_NUMBER関数が使えるリレーショナルデータベース（RDB）のリストです。

• Oracle
• SQL Server
• PostgreSQL

ROW_NUMBERを使って連番を振る

では、ROW_NUMBER関数を使って、連番を振るサンプルコードを見ていきましょう。

まず最初のサンプルは、ROW_NUMBER関数で、scoresテーブルの「score」列の昇順(ASC)で連番を振るシンプルなSQLの例です。

SELECT 
  ROW_NUMBER() OVER(ORDER BY score ASC) num
, user_name
, score
FROM scores

実行結果

SQLを実行すると、次の表のように「score」の昇順で連番が振られているのが分かります。

次は、パーティションカラムを指定して、グループ毎に行番号の連番を振るサンプルを見ていきましょう。

次のSQLは、パーティションカラムに「city」を指定し、市区町村ごとに「score」の昇順で１～の連番を振っています。

SELECT
  ROW_NUMBER() OVER(PARTITION BY City ORDER BY Age ASC) num
  name
, city
, score
FROM scores

実行結果

順位を付けるならRANK関数

RANK関数はSQLの結果セットに対し順位を付ける関数で、ROW_NUMBER関数と機能が似ています。

RANKとROW_NUMBER関数の違いは、重複しする値に対する順位（連番）の付け方が異なります。実際にSQLを作成し、RANKとROW_NUMBER関数の違いを確認していきましょう。

以下のサンプルSQLは、scoresテーブルのscore（点数）をROW_NUMBERとRANKでそれぞれ降順で連番を振るSQLです。

SELECT 
  Name
, Score
, ROW_NUMBER() OVER(ORDER BY Score DESC) Rank
, ROW_NUMBER() OVER(ORDER BY Score DESC) Num
FROM scores

実行結果

上記の通り、Rank関数では同じ値には同じ連番順位）が割り振られ、ROW_NUMBER関数は他と重複しない連番が振られている事が分かります。

【関連記事】
▶【SQL】データを順位づけするRANK関数の使い方を解説

MySQLではユーザ定義変数を使って行番号を振る

MySQLではROW_NUBER関数が使えません。
「ユーザ定義変数」を使用するとで、MySQLでもROW_NUMBER関数と同等のことが行えます。

MySQLの「ユーザ定義変数」とは

「ユーザ定義変数」とは、MySQLのセッション上に定義する変数です。
同じDBセッション間で値を受け渡す時や、今回紹介するユーザ定義変数を使った連番を振る時などに使用します。

サンプルコード

次のSQLは、usersテーブルの年齢（age）の昇順に連番を振る例です。
ポイントは、@rownumを宣言している１行目や、@rownumを使った処理の部分です。

SET @rownum=0;

SELECT 
  @rownum:=@rownum+1 as num
, user_name
, score
FROM scores
ORDER BY score

実行結果

ROWNUM疑似列

Oracleデータベースを使用している場合、ROWNUM疑似列を使って行番号を振る事もできます。

ROWNUM疑似列は、SQLの結果セットからソート前のレコード番号を返す擬似列です。ROWNUM擬似列は、SELECT文の結果セットに暗黙的に追加され、どのSQLからでも参照できます。

ROWNUMの基本的な使い方

ROWNUM擬似列は、次のようにSELECT句にROWNUMと指定するだけで取得することができます。
次のSQLでは、usersテーブルから取得したレコードに対し、ROWNUMで行番号を取得しています。

SELECT 
  ROWNUM 
, Name
, Age
FROM users

| ROWNUM | Name          | Age  |
| ------ | ------------- | ---- |
| 1      | Yamada Taro   | 10   |
| 2      | Suzuki Ichiro | 30   |
| 3      | Sato Taro     | 20   |
| 4      | Tanaka Jiro   | 50   |
| 5      | Ito Hanako    | 40   |

ORDER BYとROWNUMを同時に使う時は注意

ROWNUMとORDER BYを同時に使用すると、想定外の結果が発生するため注意が必要です。

先述したとおり、ROWNUM擬似列は「ソート前のレコード番号を表す擬似列」です。
そのため、ROWNUMで先に連番を振った後にORDER BY句によって結果のソートが行われるため、次のようなSQLを実行すると、正しく連番が取得できません。

SELECT 
  ROWNUM 
, Name
, Age
FROM users
ORDER BY Age DESC

| ROWNUM | Name          | Age  |
| ------ | ------------- | ---- |
| 4      | Tanaka Jiro   | 50   |
| 5      | Ito Hanako    | 40   |
| 2      | Suzuki Ichiro | 30   |
| 3      | Sato Taro     | 20   |
| 1      | Yamada Taro   | 10   |

上の例では、ROWNUM擬似列でまず結果セットに対して連番が振られ、その後「Age」列でソートが行われるため、ROWNUMの値が１〜の連番になりません。

さいごに

SQLで行番を振る方法について解説してきました。データのランク付けなどにも使われるROW_NUMBER関数は、色々のシステム開発で利用するため、是非覚えておきましょう。