title: Why cant we use rank() analytic function to delete duplicates in a table?
body: <p>I have created an emp table with the following records in it.</p>
  
  <pre><code>    create table emp(
      EMPNO  integer,
      EMPNAME varchar2(20),
      SALARY  number);
  
  select * from emp;
  
  empno empname  salary
  10    bill     2000
  11    bill     2000
  12    mark     3000
  12    mark     3000
  12    mark     3000
  12    philip   3000
  12    john     3000
  13    tom      4000
  14    tom      4000
  14    jerry    5000
  14    matt     5000
  15    susan    5000
  </code></pre>
  
  <p>To delete duplicates i have been using the rownum() function along with partition by and order by clause with the query as follows:</p>
  
  <pre><code>delete from emp where rowid in
  (
  select rid from
  (
  select rowid rid,
  row_number() over(partition by empno order by empno) rn
  from emp
  )
  where rn &gt; 1
  );
  --6 rows deleted
  </code></pre>
  
  <p>The query deletes all the employee records with duplicate empno's and the result looks somethin like this:</p>
  
  <pre><code>empno empname  salary
  10    bill     2000
  11    bill     2000
  12    mark     3000
  13    tom      4000
  14    tom      4000
  15    susan    5000
  </code></pre>
  
  <p>When i use the inner query to fetch the rownumbers for all the results in the table it gives me the following result:</p>
  
  <pre><code>select rowid as rid,empno,empname,
  row_number() over(partition by empno order by empno) rn
  from emp;
  
  rowid                         rownumber
  AACDJUAAPAAGLlTAAA  10  bill    1
  AACDJUAAPAAGLlTAAB  11  bill    1
  AACDJUAAPAAGLlTAAE  12  mark    1
  AACDJUAAPAAGLlTAAD  12  mark    2
  AACDJUAAPAAGLlTAAC  12  mark    3
  AACDJUAAPAAGLlTAAF  12  philip  4
  AACDJUAAPAAGLlTAAG  12  john    5
  AACDJUAAPAAGLlTAAH  13  tom     1
  AACDJUAAPAAGLlTAAI  14  tom     1
  AACDJUAAPAAGLlTAAJ  14  jerry   2
  AACDJUAAPAAGLlTAAK  14  matt    3
  AACDJUAAPAAGLlTAAL  15  susan   1
  </code></pre>
  
  <p>But when i use rank() in place of the rownumber() function it gives me the following result:</p>
  
  <pre><code>select rowid as rid,empno,empname,
  rank() over(partition by empno order by empno) rn
  from emp;
  
  rowid                          rank
  AACDJUAAPAAGLlTAAA  10  bill    1
  AACDJUAAPAAGLlTAAB  11  bill    1
  AACDJUAAPAAGLlTAAE  12  mark    1
  AACDJUAAPAAGLlTAAD  12  mark    1
  AACDJUAAPAAGLlTAAC  12  mark    1
  AACDJUAAPAAGLlTAAF  12  philip  1
  AACDJUAAPAAGLlTAAG  12  john    1
  AACDJUAAPAAGLlTAAH  13  tom     1
  AACDJUAAPAAGLlTAAI  14  tom     1
  AACDJUAAPAAGLlTAAJ  14  jerry   1
  AACDJUAAPAAGLlTAAK  14  matt    1
  AACDJUAAPAAGLlTAAL  15  susan   1
  </code></pre>
  
  <p>So my question here is why does rank() give the same value to all the records in the table even though there are duplicate empid's?</p>