title: How to get the latest employee record in oracle?
body: <p>I have a employee table with columns like emp_id, firstname, lastname,  region_id, status and effective_date. </p>
  
  <p>Employee Table can have multiple entries for same employee with different effective dates and statuses. </p>
  
  <p>Employee can have two statuses 'Leaver' and 'Joiner'. </p>
  
  <pre><code>id     emp_id    firstname     region    status     effective_date  
  1       1         James        Asia      Joiner     1-Jan-2012 
  2       1         James        UK        Leaver     1-Aug-2012
  3       1         James        USA       Joiner     1-Aug-2012
  4       1         James        Asia      Leaver     1-May-2012
  5       1         James        UK        Joiner     1-May-2012
  6       1         James        USA       Leaver     1-Sep-2012
  </code></pre>
  
  <p>With the above data in employee table, If i want to get the latest record of james as on 1 Jan 2012, I would get record with id = 1, </p>
  
  <p>If i want to get the latest record of james as on 1 May 2012, I would get record with id = 5</p>
  
  <p>If i want to get the latest record of james as on 1 Aug 2012, I would get record with id = 3,</p>
  
  <p>If i want to get the latest record of james as on 1 Sep 2012, I would get record with id = 6</p>
  
  <p>Following query correctly gives me latest record </p>
  
  <pre><code>SELECT 
          emp_id, 
          MAX(effective_date) AS latest_effective_date
  FROM 
          EMPLOYEE
  GROUP BY 
          emp_id
  </code></pre>
  
  <p>But then how do I get the other columns such as firstname , region etc. </p>
  
  <p>If I put them in select clause or group by clause, I dont just get the latest record but the other records as well.</p>