title: How To Rank In Oracle SQL Per Group For Whole Table?
body: <p>I am using Oracle SQL Developer to attempt to automatically calculate periods of absence from an HR table.  This table has the following relevant fields:
  Person_Ref - Unique person identifier
  START_DATE - The date that a period of absence commenced.
  COMPDATE - Seems to be a 6-digit number which counts downwards, so lowest number is most recent.
  I ultimately wish to write a statement that will rank the absence start dates for every person, take the newest START_DATE (i.e. lowest COMPDATE), and the 3rd newest START_DATE, then mathematically calculate the number of days between them (n.b. this table contains decades of absence, each entry indexed against PERSON_REF).  This is a business requirement to report on cases where there are more than 3 periods of absence in 12 months (i.e. have the start dates of the last 3 periods of absence all fallen within 12 months).</p>
  
  <p>I intend to firstly extract PERSON_REF, newest START_DATE, 3rd newest START_DATE, and COMPDATE into a separate table, hopefully with one line per PERSON_REF, then do the rest of the logic in Visual Studio.  However this is where my SQL knowledge lets me down.</p>
  
  <p>I can easily get ranked dates returned if I run this statement for a specific PERSON_REF:</p>
  
  <pre><code>SELECT D57.PERSON_REF, D57.START_DATE, D57.COMPDATE, RANK() OVER (ORDER BY D57.COMPDATE asc) rnk
  From D57
  WHERE D57.PERSON_REF='050050713';
  </code></pre>
  
  <p>From there I can pick the 1st &amp; 3rd dates from the results in the rest of my query.</p>
  
  <p>However, if I removed the WHERE command, then obviously it just ranks the entire table in order.</p>
  
  <p>What I really want to know is, how can I write this sub query to return ranked results per PERSON_REF for every PERSON_REF in the table?</p>