title: Oracle SQL Partitions and Rank
body: <p>I want to be able to group data by effective dates for the most current project for employees, including department, and manager they work for. Here is a sample of the data.  </p>
  
  <p><strong>PROJ_TBL</strong></p>
  
  <pre><code>+-------------+----------+----------------+
  | EMPLOYEE_ID | EFF_DATE | EMPL_PROJECT   |
  +-------------+----------+----------------+
  | P1441       | 05/21/11 | IMC            |
  | P1441       | 09/12/12 | BEEB           |
  | P1441       | 09/23/12 | PRUD_FIN_SALES |
  +-------------+----------+----------------+
  </code></pre>
  
  <p><strong>EMPLOYEE_TBL</strong></p>
  
  <pre><code>+-------------+--------------+---------+----------+
  | EMPLOYEE_ID | PROJECT_MBR  | DEPT_NM | EFF_DATE |
  +-------------+--------------+---------+----------+
  | P1441       | BEN DEENEY   | ACCNT   | 02/09/08 |
  | P1566       | LAURA FIELDS | ACCNT   | 05/03/10 |
  | P2155       | PAUL DAVEY   | ACCNT   | 10/03/10 |
  | P1441       | BEN DEENEY   | SALES   | 07/19/12 |
  +-------------+--------------+---------+----------+
  </code></pre>
  
  <p><strong>EMP_DPT_TBL</strong></p>
  
  <pre><code>+-------------+---------------+---------+----------+
  | EMPLOYEE_ID | MANAGER       | DEPT_NM | EFF_DATE |
  +-------------+---------------+---------+----------+
  | P1441       | BOB PAISLEY   | ACCNT   | 02/09/08 |
  | P1441       | LINDA HARDY   | SALES   | 07/19/12 |
  +-------------+---------------+---------+----------+
  </code></pre>
  
  <p>I'm not very familiar with the use of partitions.  I want to be able to use it to combine the information to get the current data for <code>EMPLOYEE_ID</code> <code>P1441</code>. The desired output is :</p>
  
  <pre><code>+-------------+---------+--------------+----------------+
  | EMPLOYEE_ID | DEPT_NM | MANAGER      | PROJECT        |
  +-------------+---------+--------------+----------------+
  | P1441       | SALES   | LINDA HARDY  | PRUD_FIN_SALES |
  +-------------+---------+--------------+----------------+
  </code></pre>
  
  <p>I am able to get the correct current records for the individual tables but combining the results to generate what I want is problematic.  Here are the queries...</p>
  
  <pre><code>-- Current Project
  SELECT EMPL_PROJECT,
         EMPLOYEE_ID,
         EFF_DT
  FROM   ( SELECT EMPL_PROJECT,
                  EMPLOYEE_ID,
                  EFF_DT,
                  RANK() OVER ( PARTITION BY EMPLOYEE_ID
                                ORDER BY EFF_DT DESC) AS rk1
          FROM    PROJ_TBL ) t
  WHERE  rk1 = 1
  
  -- Current Department
  SELECT DEPT_NM,
         EMPLOYEE_ID,
         EFF_DT
  FROM   ( SELECT DEPT_NM,
                  EMPLOYEE_ID,
                  EFF_DT,
                  RANK() OVER ( PARTITION BY EMPLOYEE_ID
                                ORDER BY EFF_DT DESC ) AS rk2 
           FROM   EMPLOYEE_TBL ) t
  WHERE  rk2 = 1
  
  -- Current Manager
  SELECT MANAGER,
         EMPLOYEE_ID,
         EFF_DT
  FROM   ( SELECT MANAGER,
                  EMPLOYEE_ID,
                  EFF_DT,
                  RANK() OVER ( PARTITION BY EMPLOYEE_ID
                                ORDER BY EFF_DT DESC ) AS rk3
           FROM   EMP_DPT_TBL ) t
  WHERE  rk3 = 1
  </code></pre>
  
  <p>How can I combine these into one query using the <code>EMPLOYEE_ID</code> to generate the report? </p>