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了！