Oracle高级查询之OVER (PARTITION BY ..), 解决查询分组后某列最大的那条数据。

基本方式：

SELECT E.ID,E.NAME, E.AGE, E.INFO
FROM EMPLOYEE E,
(SELECT E.ID, MAX( E.AGE) E.AGE FROM EMPLOYEE E GROUP BY E.ID) T
WHERE E.ID= T.ID
AND E.AGE= T.AGE;

利用OVER (PARTITION BY ..)：

SELECT T.ID, T.NAME, T.AGE, T.INFO
FROM (SELECT E.*, ROW_NUMBER() OVER(PARTITION BY E.ID ORDER BY E.AGE DESC) AS NUM
FROM EMPLOYEE E
) T
WHERE T.NUM = 1

详细资料：http://www.cnblogs.com/shined/archive/2013/01/16/2862809.html