OracleではLimit句が使えない

11月 28, 2006

OracleではLimit句が使えない

ページングでSelect結果の一部のデータをしたいとき、PostgreSQLやMySQLで使用できるLimit句はとても便利。たとえばSelect結果の10から14番目までを取得するには

select * from emp where Limit 10,5

でもOracleではLimit句は使えない...

ということでOracleではROWNUMを使用してやってみる。(Oracle10g Windows版で検証)
ところがこのROWNUMは一筋縄ではいかないのであった。

Order By 句を使って並べ替えをすると、ROWNUMが...

select rownum rn
      ,t.住所コード
      ,t.市区町村名
      ,t.住所1
      ,t.住所2
  from tb_住所マスタ t
 order by 住所2;

結果

	RN	住所コード	市区町村名	住所1	住所2
1	175	3620300230056	小松島市	坂野町	か里や開
2	187	3620300230068	小松島市	坂野町	さんた
3	190	3620300230071	小松島市	坂野町	のより
4	157	3620300230038	小松島市	坂野町	シヅ田
5	142	3620300230023	小松島市	坂野町	シャウ内
6	76	3620300200002	小松島市	和田津開町	ヒガシ
7	165	3620300230046	小松島市	坂野町	ミぞ口
8	204	3620300240001	小松島市	間新田町	ヤケ木
9	234	3620300010008	小松島市	小松島町	井利ノ口
10	336	3620300100015	小松島市	日開野町	井理守

しょーがないので副問い合わせを使用するとなんとか並ぶ

select rownum
      ,s.*
  from (select rownum rn,
              t.住所コード
              ,t.市区町村名
              ,t.住所1
              ,t.住所2
          from tb_住所マスタ t
         order by 住所2
        ) s

結果

	ROWNUM	RN	住所コード	市区町村名	住所1	住所2
1	1	175	3620300230056	小松島市	坂野町	か里や開
2	2	187	3620300230068	小松島市	坂野町	さんた
3	3	190	3620300230071	小松島市	坂野町	のより
4	4	157	3620300230038	小松島市	坂野町	シヅ田
5	5	142	3620300230023	小松島市	坂野町	シャウ内
6	6	76	3620300200002	小松島市	和田津開町	ヒガシ
7	7	165	3620300230046	小松島市	坂野町	ミぞ口
8	8	204	3620300240001	小松島市	間新田町	ヤケ木
9	9	234	3620300010008	小松島市	小松島町	井利ノ口
10	10	336	3620300100015	小松島市	日開野町	井理守

で10番目から14番目を取得しよう！と意気揚々であったのだが

select rownum
      ,s.*
  from (select rownum rn,
              t.住所コード
              ,t.市区町村名
              ,t.住所1
              ,t.住所2
          from tb_住所マスタ t
         order by 住所
        ) s
 where rownum >= 10 and rownum < 15

結果

	ROWNUM	RN	住所コード	市区町村名	住所1	住所2

レコードが返ってこない！なんで！

ROWNUMというのはそもそも結果セットに対して順番につけている番号だからして、それをさらに選択はできるのはおかしいということらしい。
そのためOracle 7 まではこれでよかったのだが、Oracle8からはダメになったそうだ。orz

副問い合わせで取得したROWNUMに別名をつけてやっと選択できた。

select * from 
(
select rownum rn2
      ,s.*
  from (select rownum rn,
              t.住所コード
              ,t.市区町村名
              ,t.住所1
              ,t.住所2
          from tb_住所マスタ t
         order by 住所2
        ) s 
) a
 where a.rn2 >= 10 and a.rn2 < 15

結果

	RN2	RN	住所コード	市区町村名	住所1	住所2
1	10	336	3620300100015	小松島市	日開野町	井理守
2	11	265	3620300040007	小松島市	堀川町	井理守
3	12	284	3620300070020	小松島市	金磯町	一番町
4	13	77	3620300200003	小松島市	和田津開町	鵜ノ松
5	14	396	3620300140003	小松島市	江田町	姥ヶ懐

さらに改良。minus句を使用する方がパフォーマンスがよいらしいです。

select rownum
      ,s.*
  from (select rownum rn,
              t.住所コード
              ,t.市区町村名
              ,t.住所1
              ,t.住所2
          from tb_住所マスタ t
         order by 住所2
        ) s
where rownum <= 14
minus
select rownum
      ,s.*
  from (select rownum rn,
              t.住所コード
              ,t.市区町村名
              ,t.住所1
              ,t.住所2
          from tb_住所マスタ t
         order by 住所2
        ) s
where rownum < 10;