ROW_NUMBER OVER PARTITION BY

17 May 2019

Reading time ~1 minute

각 CLASS 별 순위 매기기

ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY SCORE ASC)

SELECT CLASS
     , SCORE
     , ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY SCORE DESC) AS RNK
  FROM (
SELECT 'A' AS CLASS, 100 AS SCORE FROM DUAL UNION ALL
SELECT 'A' AS CLASS, 70 AS SCORE FROM DUAL UNION ALL
SELECT 'A' AS CLASS, 60 AS SCORE FROM DUAL UNION ALL
SELECT 'A' AS CLASS, 80 AS SCORE FROM DUAL UNION ALL
SELECT 'B' AS CLASS, 90 AS SCORE FROM DUAL UNION ALL
SELECT 'B' AS CLASS, 50 AS SCORE FROM DUAL UNION ALL
SELECT 'C' AS CLASS, 20 AS SCORE FROM DUAL UNION ALL
SELECT 'C' AS CLASS, 60 AS SCORE FROM DUAL UNION ALL
SELECT 'C' AS CLASS, 40 AS SCORE FROM DUAL UNION ALL
SELECT 'C' AS CLASS, 10 AS SCORE FROM DUAL
);

OUTPUT

CLASS	SCORE	RNK
A	100	1
A	80	2
A	70	3
A	60	4
B	90	1
B	50	2
C	60	1
C	40	2
C	20	3
C	10	4