title: SELECT biggest row from a LEFT JOIN
body: <p>I have a SQL query that LEFT JOINs a table into it.  This causes rows from the main table to be duplicated but with different rows from the JOINed table.  How do I only select the rows with the highest date from the JOINed table.</p>
  
  <p>Here's an example (this is the result from my query):</p>
  
  <pre><code>ID    Message    Date
  ---------------------------
  0     Hi         2011-01-01
  0     Bye        2011-02-05
  0     Hello      2011-04-20
  1     Test       2010-12-31
  1     Testing    2010-11-15
  2     Something  2010-12-12
  2     Nothing    2011-01-01
  2     Yes        2010-02-05
  3     Cool       NULL
  </code></pre>
  
  <p>I want one row per ID, the row with the highest ID.</p>
  
  <pre><code>ID    Message    Date
  ---------------------------
  0     Hello      2011-04-20
  1     Test       2010-12-31
  2     Nothing    2011-01-01
  3     Cool       NULL
  </code></pre>
  
  <p>My current query is something like this (I just kinda made this up, but it's similar to the real one):</p>
  
  <pre><code>SELECT t1.ID, t2.Message, t2.Date
  FROM t1
  LEFT JOIN (
    SELECT t3.ID, t3.message, t3.Date
    FROM t3
    LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
    WHERE t4.color = 'blue'
  ) AS t2
  ON t1.ID = t2.ID
  WHERE t1.userID = 12
  </code></pre>
  
  <p>I guess I could use PHP and loop through the results and pick out the ones I want, but can I have MySQL do that for me?</p>
  
  <p><strong>EDIT</strong>: Sorry, my 1st example was <em>way</em> wrong, this is more like what I want.</p>
  
  <p><strong>EDIT 2</strong>: I tried using GROUP BY and MAX, but I think I'm doing something wrong.</p>
  
  <p>I tried:</p>
  
  <pre><code>SELECT t1.ID, t2.Message, MAX(t2.Date)
  FROM t1
  LEFT JOIN (
    SELECT t3.ID, t3.message, t3.Date
    FROM t3
    LEFT JOIN t4 ON t4.userID = 12 AND t3.ID = t4.ID
    WHERE t4.color = 'blue'
  ) AS t2
  ON t1.ID = t2.ID
  WHERE t1.userID = 12
  GROUP BY t1.ID
  </code></pre>
  
  <p>But, that gave me:</p>
  
  <pre><code>ID    Message    Date
  ---------------------------
  0     Hi         2011-04-20
  1     Test       2010-12-31
  2     Something  2011-01-01
  3     Cool       NULL
  </code></pre>
  
  <p>How do I get the Message associated with the highest date.</p>