検索結果の最初の1件のみを取得する

稀に SQL の検索結果の最初の1件だけが欲しい場合がある（副問い合わせのときなど）。やり方はいろいろあると思うが、ここでは Oracle の ROWNUM を使うやり方をメモしておく。

準備

以下のテーブルを用意する。

T_SAMPLE:

create table T_SAMPLE (
    PARENT varchar2(6) not null,
    CHILD  varchar2(6) not null,
    SORTNO number(2,0) default 1 not null,
    constraint PK_T_SAMPLE primary key (PARENT, CHILD) using index
)
/
insert into T_SAMPLE (PARENT, CHILD, SORTNO) values ('IDC8JT', 'IDVZ1O', 1)
/
insert into T_SAMPLE (PARENT, CHILD, SORTNO) values ('IDF8CM', 'IDVZ1O', 2)
/
insert into T_SAMPLE (PARENT, CHILD, SORTNO) values ('IDCQJJ', 'IDI50N', 1)
/
insert into T_SAMPLE (PARENT, CHILD, SORTNO) values ('ID3JF6', 'IDVZ1O', 3)
/

PARENT*	CHILD*	SORTNO
'IDC8JT'	'IDVZ1O'	1
'IDF8CM'	'IDVZ1O'	2
'IDCQJJ'	'IDI50N'	1
'ID3JF6'	'IDVZ1O'	3

（* は primary key 項目）

ROWNUM を使って最初の1件のみを取得する

select PARENT, SORTNO from T_SAMPLE where CHILD = 'IDVZ1O'

これで以下の結果を取得できる。

PARENT	SORTNO
'IDC8JT'	1
'IDF8CM'	2
'ID3JF6'	3

ここで SORTNO が最大のレコードの PARENT のみが欲しい場合はこうする。

select TBL.PARENT as PARENT
  from (select PARENT, SORTNO from T_SAMPLE where CHILD = 'IDVZ1O' order by SORTNO desc) TBL
 where rownum = 1

PARENT
'ID3JF6'

元の SQL 文に rownum = 1 条件を入れると order by でソートする前の状態で条件が効いてしまうので、いったん

select PARENT, SORTNO from T_SAMPLE where CHILD = 'IDVZ1O' order by SORTNO desc

でソートした状態に対して rownum = 1 条件をセットする。

まっ、本当はこんな羽目に陥らないようにテーブル設計すべきなんだろうけど、改造仕事はこういうイレギュラーがよくあるのよ。

ROWNUM を使いたくないなら

今回の例で CHILD に対して SORTNO 値のダブりがないと言えるなら

select PARENT
  from T_SAMPLE
 where CHILD = 'IDVZ1O'
   and SORTNO = (select max(SORTNO) from T_SAMPLE where CHILD = 'IDVZ1O')

という手が使えなくもない。ん～、ちょっと苦しいかな？ テーブルのフルアクセスが2回発生するからなぁ。

【おまけ】 LIMIT を使って最初の1件のみを取得する

ついでに。
MySQL の場合は ROWNUM ではなく LIMIT が使える。

select PARENT, SORTNO from T_SAMPLE where CHILD = 'IDVZ1O' order by SORTNO desc limit 1

Oracle の ROWNUM とちがい， LIMIT の場合は order by を含めた文全体に対して効く。