title: Get the latest record within a date range in one table based on the dates in another
body: <p>I have the following tables in an Oracle 10 database.</p>
  
  <p>TABLE_A that lists IDs and an associated date:</p>
  
  <pre><code>ID         DATE
  -----------------------
  A1234    | 31-Dec-2008 
  B5678    | 31-Dec-2009
  A1234    | 31-Dec-2010 
  </code></pre>
  
  <p>TABLE_B that gives ratings for the IDs at month ends:</p>
  
  <pre><code>ID         RATING_DATE   RATING
  --------------------------------
  A1234    | 31-Dec-2008 | 3
  A1234    | 31-Jan-2009 | 3
  A1234    | 28-Feb-2009 | 3
  A1234    | 31-Mar-2009 | 2
  A1234    | 30-Apr-2009 | 2
  A1234    | 31-May-2009 | 3
  A1234    | 31-Jun-2009 | 2
  A1234    | 31-Jan-2010 | 3
  A1234    | 28-Feb-2010 | 3
  B5678    | 31-Dec-2009 | 4
  B5678    | 31-Dec-2010 | 4
  B5678    | 31-Jan-2011 | 4
  B5678    | 28-Feb-2011 | 4
  </code></pre>
  
  <p><strong>What I need:</strong></p>
  
  <p>For all the <code>ID</code>, <code>DATE</code> pairs in TABLE_A, I want to find the latest <code>RATING_DATE</code> and <code>RATING</code> in TABLE_B within the range [DATE, DATE + 12 months).</p>
  
  <p><strong>eg:</strong> For <code>A1234    | 31-Dec-2008</code>, I would be interested in finding the latest <code>RATING_DATE</code> and <code>RATING</code> within 31 Dec 2008 - 30 Nov 2009. Here, I would want <code>A1234    | 31-Jun-2009 | 2</code></p>
  
  <p>I have the following SQL to get the latest <code>RATING_DATE</code>, but I can't figure out how to get the <code>RATING</code> as well:</p>
  
  <pre><code>SELECT      A.DATE, 
              A.ID, 
              MAX(TABLE_B.RATING_DATE)
  
  FROM        TABLE_A A LEFT JOIN TABLE_B B
              ON
              A.ID = B.ID
              AND
              B.RATING_DATE &gt;= A.DATE
              AND
              B.RATING_DATE &lt; ADD_MONTHS(A.DATE, 12)
  
  GROUP BY    A.DATE, 
              A.ID
  </code></pre>
  
  <p>Is there some way of getting this done without nested joins or other sub-queries?</p>