title: Oracle SQL - How to get distinct rows using RANK() or DENSE_RANK() or ROW_NUMBER() analytic function?
body: <p>I am looking to get the top 3 distinct salaries of each department. I was able to do it either using <code>RANK()</code> or <code>DENSE_RANK()</code> or <code>ROW_NUMBER()</code> but my table is having some records with same salaries.</p>
  
  <p>Mentioned below is my query and its result.</p>
  
  <p>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).</p>
  
  <p>Please suggest/advise about how can get the distinct top 3 salaries for each department.</p>
  
  <h1>Query:</h1>
  
  <pre><code>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 &lt;= 3;
  </code></pre>
  
  <h1>RESULT:</h1>
  
  <pre><code>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
  </code></pre>