如下例所示，查找deptno=20部门中sal出现次数最多的值。

SQL> select sal

2  from

3  (

4  select sal,dense_rank() over(order by cnt desc) as rnk

5  from

6  (

7  select sal,count(*) as cnt

8  from emp

9  where deptno=20

10  group by sal

11  )

12  )

13  where rnk=1;

SAL

----------

3000

下面把这个查询分解一下，一步步看看分别得到了什么结果，便于理解。

SQL> select sal,count(*) as cnt

2  from emp

3  where deptno=20

4  group by sal;

SAL        CNT

---------- ----------

2975          1

1100          1

3000          2

800          1

SQL> select sal,dense_rank() over(order by cnt desc) as rnk

2  from

3  (

4  select sal,count(*) as cnt

5  from emp

6  where deptno=20

7  group by sal

8  );

SAL        RNK

---------- ----------

3000          1

800          2

2975          2

1100          2

对于ORACLE 9i以及更高版本，也可以使用聚集函数MAX的KEEP扩展。

SQL> select max(sal) keep(dense_rank first order by cnt desc) sal

2  from

3  (

4  select sal,count(*) as cnt

5  from emp

6  where deptno=20

7  group by sal

8  );

SAL----------      3000