ROW_NUMBER()

그룹별 ROWNUM 주기

 

SELECT 

   ROW_NUMBER() OVER(PARTITION BY COLUMN.A ORDER BY COLUMN.A, COLUMN.B) AS RNUM,

   COLUMN.A,

   COLUMN.B

FROM

   TABLE

GROUP BY COLUMN.A, COLUMN.B

 

(EXAMPLE)

SELECT ITEM_NM,
          GT
  FROM ZTMP_EI_BND

ITEM	GT
NETWORK EQUIPMENT1	6
NETWORK EQUIPMENT2	3.5
NETWORK EQUIPMENT3	3.7
NETWORK EQUIPMENT4	3.5
NETWORK EQUIPMENT5	7.1
USED NETWORK EQUIPMENT1	2.3
USED NETWORK EQUIPMENT2	6.5
USED NETWORK EQUIPMENT3	7
USED NETWORK EQUIPMENT4	6

 

 

SELECT ITEM_NM, 
          GT,

          ROW_NUMBER() OVER (ORDER BY GT) AS NUMBER
  FROM ZTMP_EI_BND

ITEM	GT	NUMBER
USED NETWORK EQUIPMENT	2.3	1
USED NETWORK EQUIPMENT	3.5	2
USED NETWORK EQUIPMENT	3.5	3
NETWORK EQUIPMENT	3.7	4
NETWORK EQUIPMENT	6	5
NETWORK EQUIPMENT	6	6
NETWORK EQUIPMENT	6.5	7
USED NETWORK EQUIPMENT	7	8
NETWORK EQUIPMENT	7.1	9