A Day In The Life

とあるプログラマの備忘録

ROWNUMよりROW_NUMBER()

ページ送り機能の実装でワークテーブルを使って実現する予定でした。

しかしOracleのROWNUM擬似列を使って実現することになりました。



このROWNUM少々厄介です。

Order byを使うと思ったデータを取得してくれないのです。

たとえば

select id,name from user where rownum <= 10

というSQL文だと10件目までのデータを正しく取ってきてくれます。
しかしこれを

select id,name from user where rownum <= 10 order by name

とすると正しくデータを取ってきてくれません。

ROWNUMの採番がORDER BYする以前に行われることが原因のようです。

この例だと、まず抽出したデータからrownum <= 10のデータをとってきてそれをORDER BYしちゃうみたいです(-_-#)

select id,name,rownum from
(select id,name from user order by name)
where rownum <= 10

とすると正しく取ってきてくれます。



じゃあ11件目から20件目のデータを取るにはどうすればいいのか?

単純に考えると

select id,name,rownum from
(select id,name from user order by name)
where rownum >= 11 and rownum <= 20

と書いてやればいいはず。

しかしこのSQL文だとデータを1件も取ってきてくれません...(;´ω`)

これを少し改良して

select id,name from
(select id,name,rownum rn from user)
where rn >= 11 and rn <= 20

とすれば11件目から20件目までのデータを取得してくれます。

やれやれ(^_^;)これで解決と思ったら大きな落とし穴が..._| ̄|○

このSQL文にORDER BY句を追加すると正しくデータを取ってきてくれません。



ROWNUMでできるのはこれが限界のようです。

そこでROW_NUMBER()という関数を使用してやるとORDER BYを使用しても正しくデータをとってきてくれます。具体的には

select id,name from
(select row_number() over(order by name) rn,id,name from user order by name)
where rn >= 11 and rn <= 20

といった感じです。

データ量が多いときのパフォーマンスが少し心配ですがこれは使えます。