Oracle SQL How to get individual rows using RANK () or DENSE_RANK () or ROW_NUMBER () analytic function?

Question

I am looking to get 3 main salaries of each department. I was able to do this either with the help of RANK(), or DENSE_RANK()or ROW_NUMBER(), but in my table there are entries with the same salary.

Below is my query and its result.

The top 3 salaries of Department 20 should be 6000, 3000, 2975. But there are 2 employees with a salary of 3000, and both of them have rank 2. Thus, he gives me 4 entries for this department (1 for rank 1, 2 entries for rank 2 and 1 entry for rank 3).

Please offer / advise how to get an excellent 3 salaries for each department.

Query:

SELECT * FROM (
SELECT EMPNO, DEPTNO, SAL, 
DENSE_RANK() over (partition by deptno order by sal DESC) as RANK,
row_number() over (partition by deptno order by sal DESC) as ROWNO
from EMP)
WHERE RANK <= 3;

RESULT:

Empno Deptno    Salary Rank   Rowno
---------------------------------------- 
7839    10  5000    1   1
7782    10  2450    2   2
7934    10  1300    3   3
7935    20  6000    1   1
7788    20  3000    2   2
7902    20  3000    2   3
7566    20  2975    3   4
7698    30  2850    1   1
7499    30  1600    2   2
7844    30  1500    3   3

Answer 1

row_number, partitioning by dept,salary, row_number dense_rank, :

with data_row as 
( 
select 7839 as empno, 10 as deptno, 5000 as salary from dual union all
select 7782 as empno, 10 as deptno, 2450 as salary from dual union all
select 7934 as empno, 10 as deptno, 1300 as salary from dual union all
select 1111 as empno, 10 as deptno, 1111 as salary from dual union all
select 7935 as empno, 20 as deptno, 6000 as salary from dual union all
select 7788 as empno, 20 as deptno, 3000 as salary from dual union all
select 7902 as empno, 20 as deptno, 3000 as salary from dual union all
select 7566 as empno, 20 as deptno, 2975 as salary from dual union all
select 2222 as empno, 20 as deptno, 2222 as salary from dual union all
select 7698 as empno, 30 as deptno, 2850 as salary from dual union all
select 7499 as empno, 30 as deptno, 1600 as salary from dual union all
select 7844 as empno, 30 as deptno, 1500 as salary from dual union all
select 3333 as empno, 30 as deptno, 1333 as salary from dual
)
select *
from
(
select 
       deptno,
       salary,
       dense_rank() over (partition by deptno order by salary desc) as drank,
       row_number() over (partition by deptno, salary order by salary desc) as rowno             

from data_row
)
where drank <=3 and
      rowno =1

Answer 2

row_number, , :

SELECT * 
FROM   (SELECT empno, deptno, sal
               DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rk,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) as rowno
        FROM   emp)
WHERE rowno <= 3;

Answer 3

Try:

SELECT empno, deptno, DISTINCT(sal)
FROM   (SELECT empno, deptno, sal
               DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rk,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) as rowno
        FROM   emp)
WHERE rowno <= 3;