取各部门工资最高人员名称

[sql]
SQL> select b.dname,  
       a.ename,  
       a.sal,  
       max(a.ename) keep(dense_rank last order by a.sal) over(partition by b.dname) as no1,  
       first_value(a.ename) over(partition by b.dname) as first_one,  
       last_value(a.ename) over(partition by b.dname) as last_one  
  from emp a  
 inner join dept b on b.deptno = a.deptno  
 order by 1, 3  
/  
  
DNAME                ENAME           SAL NO1          FIRST_ONE        LAST_ONE  
---------------------------- -------------------- ---------- -------------------- -------------------- --------------------  
ACCOUNTING           MILLER         1300 KING         MILLER           KING  
ACCOUNTING           CLARK          2450 KING         MILLER           KING  
ACCOUNTING           KING           5000 KING         MILLER           KING  
RESEARCH             SMITH           800 SCOTT        SMITH            SCOTT  
RESEARCH             ADAMS          1100 SCOTT        SMITH            SCOTT  
RESEARCH             JONES          2975 SCOTT        SMITH            SCOTT  
RESEARCH             FORD           3000 SCOTT        SMITH            SCOTT  
RESEARCH             SCOTT          3000 SCOTT        SMITH            SCOTT  
SALES                JAMES           950 BLAKE        JAMES            BLAKE  
SALES                MARTIN         1250 BLAKE        JAMES            BLAKE  
SALES                WARD           1250 BLAKE        JAMES            BLAKE  
SALES                TURNER         1500 BLAKE        JAMES            BLAKE  
SALES                ALLEN          1600 BLAKE        JAMES            BLAKE  
SALES                BLAKE          2850 BLAKE        JAMES            BLAKE  
  
14 rows selected.  
  
SQL>