【SQL】RONUMで連番を取得！Oracleで連番を取得する方法と注意点を解説

ROWNUM疑似列とは？

ROWNUM疑似列は、SQLの結果セットから、ソート前のレコード番号を表す擬似列です。
Oracle固有機能であるROWNUM擬似列は、SELECT文の結果セットに暗黙的に追加されます。
この記事では、SQLでROWNUMを使う方法と注意点を解説します。

ROWNUMの用途

ROWNUM擬似列は、主にSQLの結果に対し、連番を振りたい時に使用します。
また、データを検索する時に「最初の○行」や「○行目〜△行目」など、取得するデータの件数を絞り込みたい時にも使用します。

ROWNUMの基本的な使い方

ROWNUM擬似列は、SELECT句に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   |

上の結果のように、レコードの取得順にROWNUM擬似列に連番が振られています。

取得レコード数を制限する

WHERE句でROWNUM擬似列を使って、取得するレコード数を制限することもできます。
次のSQLは、usersテーブルからレコードを10件に絞って取得する例です。

SELECT 
  ROWNUM 
, Name
, Age
FROM users
WHERE ROWNUM <= 10

「ROWNUM = 2」は使えない

前の結果を見ると、「WHERE ROWNUM = 2」のようにして、2行目のレコードだけを取得したり、「BETWEEN 10 AND 20」のように、特定の行数の範囲に絞ってレコード取得できるのでは…？と思う方もいるでしょう。
実は、このような条件は無効で、SQLの結果は必ず0件になります。

SELECT * FROM users WHERE ROWNUM = 2
------------
レコードが選択されませんでした。

SELECT * FROM users WHERE BETWEEN 10 AND 20
------------
レコードが選択されませんでした。

上の結果に疑問を持つ方もいると思いますが、これはOracleの仕様通りの動作です。
ROWNUMは、WHEREの条件で一致したレコードに対し、１〜の連番を降ります。
つまりROWNUM = 2とした場合、最初のレコードで1 = 2の条件が不成立となり、以降のレコードもROWNUMの値が変わらず「1」のままのため、すべてのレコードで条件が不成立となり、結果としてレコードが選択されません。

ROWNUMで2レコード目だけを抽出する場合は、以下のように、一度サブクエリで連番を振ってから、親のSQLのWHEREでレコードを絞り込みます。

SELECT *
FROM (
  SELECT ROWNUM AS num
       , users.* 
  FROM users
)
WHERE num = 2

| num  | Name          | Age  |
| ---- | ------------- | ---- |
| 2    | Suzuki Ichiro | 30   |

ORDER BYを使用する時の注意

ORDER BYを使用してソートする時に、ROWNUM擬似列を使うときは注意が必要です。
冒頭で述べたとおり、ROWNUM擬似列は、「ソート前のレコード番号を表す擬似列」です。

つまり、ROWNUMで連番を振った後にORDER BYのソートが行われるため、以下のようにROWNUMの値が連番ではなくなります。

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の値が１〜の連番になりません。

ポテパンダの一言メモ

ソート前のレコードの取得順は、Oracleのオプティマイザに依存します。場合によっては、上のようなSQLでもROWNUM擬似列の値がうまく連番になるかもしれません。ただし、それは偶然で、ORDER BYを使うとROWNUM`の値は連番にならないと認識しておきましょう。

ROWNUMは速度が早い？

ROWNUMは、SQLの結果セットに暗黙的に追加される仮想列です。ではROWNUMをWHERE句に指定すると、検索スピードはどうなるでしょうか？

一般的に、データ量が多いテーブルに対して検索処理を高速化する場合、検索するキーに対して索引を追加しますが、ROWNUMには索引がないため、WHERE句に指定しても検索スピードが早くなることはありません。

ただし、ROWNUMをWHERE句に指定すると、読み込むレコード数が絞り込まれるため、「ディスク読み込み」や「ネットワーク転送量」などI/Oの部分で低減が見込まれるため、結果的にSQLの実行時間が早くなることが多いです。

ROWNUMはグループ単位に連番が振れない

ROWNUM擬似列は、SQLの結果セット全体に対して連番を振るため、例えば、ユーザーの地域毎に連番を振るなど、グループ単位に連番を振ることができません。

代わりにROW_NUMBERを使う

グループ単位に連番を振る時は、ROW_NUMBER関数を使用します。ROW_NUMBER関数を使うことで、グループ単位に連番を振ったり、ソート条件を指定して連番を振ったりできます。

以下のSQLは、ユーザーの地域(City)毎に、年齢(Age)の昇順にROW_NUMBER関数で連番を振る例です。

SELECT
  ROW_NUMBER() OVER(PARTITION BY City ORDER BY Age ASC) Num
  Name
, City
, Age
FROM users

| Num  | Name          | City   | Age  |
| ---- | ------------- | ------ | ---- |
| 1    | Yamada Taro   | Tokyo  | 10   |
| 2    | Suzuki Ichiro | Tokyo  | 20   |
| 1    | Sato Taro     | Nagoya | 30   |
| 2    | Tanaka Jiro   | Nagoya | 40   |
| 1    | Ito Hanako    | Osaka  | 50   |

ROW_NUMBER以外にも、RANK関数、DENSE_RANK関数で、順位付けした連番を振ることもできます。

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

ROWNUMはOracleでしか使えないので注意

ROWNUM擬似列で連番を取得する方法を解説してきました。
ROWNUMはOracleにしかない機能であるため、MySQL、SQL Serverなどのデータベースでは使用できません。
将来的にOracle以外を使用する予定がある場合は、ROW_NUMBER関数を使用しましょう。