title: Mysql join based on max(timestamp)
body: <p>I have a web app that lets a client define a daily rate for their service.  For any given day, the client can enter two rates, an hourly rate (rateTypeId=1) and a dailyRate (rateTypeId=2). These rates are often assigned in advance, and they often change.  I need to track all assignments, but only pull the latest assigned rate.</p>
  
  <p>I have two tables.  The first table simply defines my rate structure and looks like this (simplified):</p>
  
  <p>Table: RateDefinitions</p>
  
  <pre><code>RATECODE ----- RATE
  31 ---------------- 5.00
  32 ---------------- 6.00
  33 ---------------- 7.00
  </code></pre>
  
  <p>My second table tracks the rates assigned to given dates. More than one rate can be assigned to a given date, but we will only used the latest rate based on the 'entrytimestamp'.</p>
  
  <p>Table: Rates</p>
  
  <pre><code>ID --- RATETYPEID --- RATECODE ------ DATE -------- ENTRYTIMESTAMP
  1 ---------- 1 --------------- 31 ---------- 20091010 ---------- 1100000000
  2 ---------- 2 --------------- 33 ---------- 20091010 ---------- 1100000000
  3 ---------- 1 --------------- 32 ---------- 20091010 ---------- 1200000000
  </code></pre>
  
  <p>Now I'm having difficulty putting together a query that will pull all the latest rate assignments for a given timeframe.</p>
  
  <p>I've tried:</p>
  
  <pre><code>select r.id, r.rateTypeId, r.rateCode, max(r.entryTimestamp), rd.rate
  from rates r
  join rateDefinitions rd
  on r.rateCode=rd.rateCode
  where date=20091010
  group by startDate, rateTypeId
  </code></pre>
  
  <p>But that's not going to do it. I'm thinking I need a join on a subselect statement, but not sure. My results should contain two rows per date, similar to:</p>
  
  <pre><code>ID --- RATETYPEID --- RATECODE ---------- ENTRYTIMESTAMP ----- RATE
  3 ----------- 1 --------------- 32 -------------------- 1200000000 ----------6.00
  2 ----------- 2 --------------- 33 -------------------- 1100000000 ----------7.00
  </code></pre>
  
  <p>Thanks for any suggestions.</p>