【Oracle】 ソート後ROWNUMを使用して指定位置のレコードを取得する

 やりたいこと

テーブルを検索しAカラムで昇順ソート後、ソート順が2番目となるレコードを取得したい。
2行目を指定となるとROWNUMあたり使って取得するSQLを考えてみる


検証作業
Oracle標準のDEPTテーブルで試す
DEPTテーブルを検索しDNAMEで昇順ソート後2行目となるレコードを取得出来るSQLを作成する。

①DEPTテーブルをROWNUM付けて取得
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM DEPT
※SELECT ROWNUM, * FROM DEPT → *にするとエラーになってしまった。

ROWNUM DEPTNO DNAME LOC
"1" "10" "ACCOUNTING" "NEW YORK"
"2" "20" "RESEARCH" "DALLAS"
"3" "30" "SALES" "CHICAGO"
"4" "40" "OPERATIONS" "BOSTON"

②この状態でROWNUMを指定し結果を確認する

SELECT ROWNUM, DEPTNO, DNAME, LOC FROM DEPT WHERE ROWNUM=2
あれ結果が返ってこない
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM DEPT WHERE ROWNUM<3
これだと2行帰ってくるな。
ROWNUM DEPTNO DNAME LOC
"1" "10" "ACCOUNTING" "NEW YORK"
"2" "20" "RESEARCH" "DALLAS"


③ ②のSQLにORDER BYを追加して取得
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM DEPT　ORDER BY DNAME

ROWNUM DEPTNO DNAME LOC
"1" "10" "ACCOUNTING" "NEW YORK"
"4" "40" "OPERATIONS" "BOSTON"
"2" "20" "RESEARCH" "DALLAS"
"3" "30" "SALES" "CHICAGO"
ん～ORDER BYは結果に対して並べ替えなんでこれだとROWNUMは
元々のテーブルのROWNUMなんで指定が出来ない。

③ 検索結果の結果セットを副問合せとしROWNUMを確認する。
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM (
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM DEPT　ORDER BY DNAME
)

ROWNUM DEPTNO DNAME LOC
"1" "10" "ACCOUNTING" "NEW YORK"
"2" "40" "OPERATIONS" "BOSTON"
"3" "20" "RESEARCH" "DALLAS"
"4" "30" "SALES" "CHICAGO"
ROWNUMが期待通りに変わったので取得できそうだけど②の状態なんで
ROWNUM=2って動作するのか？

④ ROWNUMを検索条件に指定してやってみる
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM
(
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM DEPT　ORDER BY DNAME
)
WHERE ROWNUM=2
やっぱり無理かROWNUM<3として試す

ROWNUM DEPTNO DNAME LOC
"1" "10" "ACCOUNTING" "NEW YORK"
"2" "40" "OPERATIONS" "BOSTON"
ROWNUM=2って指定出来れば出来そうだが
Oracle内部のことはよくわからんがROWNUMの採番方法が関係しているのか？

⑤ さらに副問合せを用いてROWNUMを含めた形としてみる
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM
(
SELECT ROWNUM AS RNUM, DEPTNO, DNAME, LOC FROM
(
SELECT ROWNUM, DEPTNO, DNAME, LOC FROM DEPT　ORDER BY DNAME
)
)
WHERE RNUM=2

出来た！
ROWNUM DEPTNO DNAME LOC
"1" "40" "OPERATIONS" "BOSTON"

一応ROWNUMを出力すると1になっている。


結論
検証を頼まれてやっては見たが。。
ROWNUMは結果に対して付番されるってことで条件に入れると結果が正しく帰ってこないって感じなのかなって
Oracleの仕様書読めば良いかもしれないが読む気になれず。