Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I am looking to get the top 3 distinct salaries of each department. I was able to do it either using RANK() or DENSE_RANK() or ROW_NUMBER() but my table is having some records with same salaries.

Mentioned below is my query and its result.

The top 3 salaries of Dept 20 should be 6000, 3000, 2975. But there are 2 employees with salary 3000 and both of them have rank 2. So it is giving me 4 records for this department (1 for rank 1, 2 records for rank2 and 1 record for rank3).

Please suggest/advise about how can get the distinct top 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
like image 1000
Rakesh Avatar asked May 04 '14 05:05

Rakesh


People also ask

What is difference between rank () ROW_NUMBER () and DENSE_RANK () in Oracle?

row_number numbers the rows 1, 2, 3, etc by the columns in the ORDER BY clause, and if there are ties, it is arbitrary which rows that gets the same number. rank and dense_rank are similar to row_number , but when there are ties, they will give the same value to the tied values.

What is difference between rank () ROW_NUMBER () and DENSE_RANK () in SQL with example?

The row_number gives continuous numbers, while rank and dense_rank give the same rank for duplicates, but the next number in rank is as per continuous order so you will see a jump but in dense_rank doesn't have any gap in rankings.

What is the difference between the rank () and DENSE_RANK () functions?

Differences between RANK and DENSE_RANK RANK and DENSE_RANK will assign the grades the same rank depending on how they fall compared to the other values. However, RANK will then skip the next available ranking value whereas DENSE_RANK would still use the next chronological ranking value.

Which is better ROW_NUMBER or rank?

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5). ROW_NUMBER is a temporary value calculated when the query is run.


2 Answers

If you get more specific in row_number, with partitioning by dept,salary then you can combine row_number and dense_rank as in this query:

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
like image 122
zundarz Avatar answered Oct 29 '22 19:10

zundarz


The row_number function you used should do the trick:

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;
like image 42
Mureinik Avatar answered Oct 29 '22 18:10

Mureinik