title: How to join with most recent record?
body: <p>I have two tables. Table A contains daily information on transactions on corporate bonds from 2004 to 2012, and table B contains bond rating information on specific dates. I need to join the two tables, so that for each transaction i table A, the most recent rating for that specific bond is appended.</p>
  
  <pre><code>Table A: daily_transactions
  --------------------------------------------
  DATE        |BOND    |PRICE
  --------------------------------------------
  20110401    |AES     |100
  20110402    |AES     |101
  20110403    |AES     |102
  20110404    |AES     |103
  20110401    |BPP     |99
  20110402    |BPP     |98
  
  
  Table B: bond_ratings
  --------------------------------------------
  DATE        |BOND    |RATING
  --------------------------------------------
  20110401    |AES     |AAA
  20110403    |AES     |BB
  20110401    |BPP     |CCC
  
  
  Table C: joined_data
  --------------------------------------------
  DATE        |BOND    |PRICE   |RATING
  --------------------------------------------
  20110401    |AES     |100     |AAA
  20110402    |AES     |101     |AAA
  20110403    |AES     |102     |BB
  20110404    |AES     |103     |BB
  20110401    |BPP     |99      |CCC
  20110402    |BPP     |98      |CCC
  </code></pre>
  
  <p>I have approx. 1,000,000 records in table A and 14,000 records in table B.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>What I have so far is:</p>
  
  <pre><code>create table test_merge as
  SELECT a.date, b.date, a.bond, a.price, b.rating
  FROM   daily_transactions  a
  LEFT   JOIN bond_ratings b ON a.bond = b.bond AND b.date &lt;= a.date
  WHERE  NOT EXISTS (
     SELECT 1 FROM bond_ratings b1
     WHERE  b1.bond = a.bond
     AND b1.date &lt;= a.date
     AND b1.date &gt;  b.date
     );
  </code></pre>
  
  <p>It seems to work perfectly (<a href="http://sqlfiddle.com/#!3/d287f/2" rel="nofollow">http://sqlfiddle.com/#!3/d287f/2</a>), however its runs extremely slow with the amount of data that I have. Takes about 2 hours. Is there any way to optimize this to run faster?</p>
  
  <p>I'm very (very) new to sql, and any help is thus much appreciated!</p>