partition by を使って、ORACLEで、グループ毎に1行だけ取得する。

投稿日:

グループ単位で一番小さいとか、一番大きいとかだけなら、MinやMaxを使って外部結合で対応できるんだけど、1番目2番目3番目を1列で取得しなきゃいけない状態になったので、調べて解決。
「partition by」なるものを使いました。

ID name group cd
1 a 3 31
2 b 2 22
3 c 2 23
4 d 1 41
5 e 3 35
6 f 5 56
7 g  5 57
8 h 5 58
9 i 2 29
10 j  4 410

こんなデータ(実際はもっとレコード数とかあるけど)があったときに、「group」単位で、○番目の「cd」を取得したい。

例えば、「group」毎に最小の「cd」なら

ID name group cd
4 d 1 41
2 b 2 22
1 a 3 31
10 j 4 410
6 f 5 56
select *
from
  (
    select
      ID
      , name
      , cd
      , row_number() over(partition by group order by cd) row_num
    from
      table
  )
where
  row_num = 1

単純にrownumで行番号に対して○番目というやり方だと、結合条件次第で結果が変わってとても今回の用件は満たさなかったのよね。あとは、

where
  row_num = 1

の条件式を変更してやれば、○番目の取得が出来ますよと。
大きいほうから○番目の取得なら、

      , row_number() over(partition by group order by cd) row_num

のorder by をdescで降順にするなりなんなりと。
さらに、条件を変えたものをコピーしてLEFT JOINで結合しておけば、「group」単位で2個しかなくても、NULLを返してくるだけなので、問題ナッシン!

参考URL:https://ameblo.jp/lovetanpopo/entry-10280370777.html







-ORACLE
-

関連記事

[ORACLE]Oracleに接続できない時に確認すること

Oracleを新しくインストールした時に、中々接続できなかったので、その時に確認しておくべき事項のまとめ。 少なくとも、これは先にやっておいた方がいいこととか。 前提条件 前提と書くとものものしいけど …

no image

OracleでLISTAGG関数を使ったら、世界が変わった話。

11gR2からGROUP_CONCAT(複数行をまとめて1列に)が可能に http://d.hatena.ne.jp/kkz_tech/20150311/1426048592 日本の中心付近で仕様変更 …

ORACLEで他のスキーマでSQLを実行した時に、権限不足で「ORA-01031: 権限が不足しています。」のエラーになる

A5Mk2 http://www.wind.sannet.ne.jp/m_matsu/developer/a5m2/ 普段、SQL関連は、A5Mk2っていうソフトを使用しています。 今回のエラーは、自 …

ORACLEでTO_CHARを使用し、カンマ区切りデータを出力する

帳票出力を行うPGで困った。 数値形式のデータのため、カンマ区切りで表示したいんだけど、テンプレートの制約のせいで上手く行かなくて時の解決方法。 帳票のテンプレートとなるファイルのセルは文字列形式。 …

Oracleで「Ora-28001:the password has expired」のエラーの対処法

正直、自分で見たときに何が起こったのかわかりませんでしたw だって、初めて見るエラーだったし・・・ ということで、調べてみたら、パスワードの有効期限らしいですね。 対処法も簡単なので、サクッと対処法を …


カテゴリー