ウィンドウ関数を使ってグルーピングの先頭を抽出する

SQL

OVER() の PARTITION BY でグルーピングした中で、ORDER BY した先頭を抽出する。

DBは、Oracle 、PostgreSQL、MySQL も同じ

例）テーブル JOB_RESULTS を JOB_CD 列でグルーピングして
　　POINT列 の ORDER BY DESC の結果、先頭を抽出する。

ROW_NUMBER() を OVER( PARTITION BY グルーピング列 ORDER BY 値 ) で、
グループ内連番を振って、先頭を抽出する方法

WITH JOB_WORKER  AS (
    SELECT
         WORKER_ID
        ,JOB_CD
        ,POINT
        ,ROW_NUMBER() OVER(PARTITION BY JOB_CD ORDER BY POINT DESC) AS RNUM
        ,SUM(POINT)   OVER(PARTITION BY JOB_CD) AS SUM_POINT
    FROM JOB_RESULTS
    WHERE DELETE_FLG = 0
)
SELECT * FROM JOB_WORKER
WHERE RNUM = 1

RANK関数を OVER( PARTITION BY グルーピング列 ORDER BY 値 ) で実行して、
先頭を抽出する方法

WITH JOB_WORKER  AS (
    SELECT
         WORKER_ID
        ,JOB_CD
        ,POINT
        ,RANK()     OVER(PARTITION BY JOB_CD ORDER BY POINT DESC) AS P_RANK
        ,SUM(POINT) OVER(PARTITION BY JOB_CD) AS SUM_POINT
    FROM JOB_RESULTS
    WHERE DELETE_FLG = 0
)
SELECT * FROM JOB_WORKER
WHERE P_RANK = 1

WITH じゃなくてサブクエリで書くと、、、

SELECT * FROM (
    SELECT
         WORKER_ID
        ,JOB_CD
        ,POINT
        ,ROW_NUMBER() OVER(PARTITION BY JOB_CD ORDER BY POINT DESC) AS RNUM
        ,SUM(POINT)   OVER(PARTITION BY JOB_CD) AS SUM_POINT
        ,RANK()       OVER(PARTITION BY JOB_CD ORDER BY POINT DESC) AS P_RANK
    FROM JOB_RESULTS
    WHERE DELETE_FLG = 0
) a
WHERE a.RNUM = 1
-- WHERE a.P_RANK = 1