oracle ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

工作中遇到的一個問題，需要對某列進行分組排序，取其中排序的第一條數據項

用到了ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)來解決此問題。

實例準備：

CREATE OR REPLACE TABLE EMPLOYEE (
  EMPID INT,
  DEPTID INT,
  SALARY DECIMAL(10,2)        
);

INSERT INTO EMPLOYEE VALUES(1,10,234.00);
INSERT INTO EMPLOYEE VALUES(2,10,1233.00);
INSERT INTO EMPLOYEE VALUES(3,20,600.00);
INSERT INTO EMPLOYEE VALUES(4,20,4512.00);
INSERT INTO EMPLOYEE VALUES(5,30,3424.00);
INSERT INTO EMPLOYEE VALUES(6,30,1232.00);
INSERT INTO EMPLOYEE VALUES(7,40,4445.00);
INSERT INTO EMPLOYEE VALUES(8,40,9999.00);
INSERT INTO EMPLOYEE VALUES(9,40,212000.00);

進行查詢操作：

SELECT DEPTID
       ,SALARY
       ,ROW_NUMBER() OVER(PARTITION BY DEPTID ORDER BY SALARY DESC) AS RN
  FROM EMPLOYEE;

執行結果：

從上面的結果可以看出來是對部門進行分組，按照薪水來進行排序，之後的處理可以根據自己的需求來進行處理就OK了！