title: SQL join between two tables with MAX in where clause
body: <p>I have <code>table_A</code> with an <code>ID</code> column. </p>
  
  <pre><code>-----
  ID
  -----
  id1
  id2
  id3
  -----
  </code></pre>
  
  <p>I have another <code>table_B</code> which has <code>latest_sub_id</code> for every <code>id</code> in <code>table_A</code> along with a date. </p>
  
  <pre><code>---------------------------------
  ID        SUB_ID     Date
  ---------------------------------
  id1       sub_id_1   2015-01-03
  id1       sub_id_2   2015-01-10
  id2       sub_id_1   2015-01-02
  id2       sub_id_2   2015-01-10
  id2       sub_id_2   2015-01-12
  id2       sub_id_3   2015-01-15
  id3       sub_id_1   2015-01-09
  id3       sub_id_2   2015-01-25
  ---------------------------------
  </code></pre>
  
  <p>I want to write a join query between the two tables, such that I get the <code>SUB_ID</code> value for given <code>ID</code> value on the <code>MAX(DATE)</code>.</p>
  
  <p>The result should be:</p>
  
  <pre><code> ---------------------------------------------------------------
   ID    SUB_ID
   ---------------------------------------------------------------
   id1   sub_id_2      ---&gt; as 10th Jan is the latest date for id1
   id2   sub_id_3      ---&gt; as 15th Jan is the latest date for id2
   id3   sub_id_2      ---&gt; as 25th Jan is the latest date for id3
   ---------------------------------------------------------------
  </code></pre>