Ok, given the clarification I can show you a way to get this in 815 and before (slow on a
big table) and in 816 and up (fast with analytic functions!)
ops$tkyte@ORA817.US.ORACLE.COM> select * from t;
DEPTNO SAL
———- ———-
10 100
10 100
10 200
10 300
10 400
10 500
20 100
20 200
20 300
20 400
20 500
20 600
20 700
20 700
30 100
30 200
30 300
30 400
18 rows selected.
Thats my sample data. Now to get it in 815 and before:
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> select distinct *
2 from t t1
3 where 5 >= ( select count(distinct t2.sal)
4 from t t2
5 where t2.deptno = t1.deptno
6 and t2.sal >= t1.sal )
7 /
DEPTNO SAL
———- ———-
10 100
10 200
10 300
10 400
10 500
20 300
20 400
20 500
20 600
20 700
30 100
30 200
30 300
30 400
14 rows selected.
And now using a new feature of 816:
ops$tkyte@ORA817.US.ORACLE.COM> select distinct *
2 from ( select deptno, sal,
3 dense_rank() over ( partition by deptno order by sal desc ) rank
4 from t )
5 where rank <= 5
6 /
DEPTNO SAL RANK
———- ———- ———-
10 100 5
10 200 4
10 300 3
10 400 2
10 500 1
20 300 5
20 400 4
20 500 3
20 600 2
20 700 1
30 100 4
30 200 3
30 300 2
30 400 1
14 rows selected.