Oracleのrow_numberとDISTINCTを組み合わせて使うと重複が排除されない。

DISTINCTとrow_numberを合わせて使うとDISINCTの前にrow_numberが行われるためか、同じレコードで違う連番が振られてしまう。

下記のようなテーブルがあったとして、sectionとその連番を取得したいとする。

Staff

section	department	id
A	1	001
A	2	002
B	1	003
B	2	004

SQLはこんな感じ。

SELECT DISTINCT
       section
     , row_number() OVER (PARTISION BY section
                          ORDER BY section) section_cnt
  FROM Staff

上記のSQLだと、DISTINCTを実行する前にrow_numberが実行されてしまうため、下記のような検索結果になってしまう。

section	section_cnt
A	1
A	2
B	1
B	2

本当は下記のように検索したい。

section	section_cnt
A	1
B	2

一旦、DISTINCTで取得したデータから連番を取得することで、重複を排除した後の連番が取得できる。

SELECT T1.section
     , row_number() OVER (PARTISION BY T1.section
                          ORDER BY T1.section) section_cnt
  FROM (SELECT DISTINCT
               section
          FROM Staff
       ) T1