title: MySQL group by and where
body: <p>Say I have a sql table: table1 with records look like this:</p>
  
  <pre><code>id1    id2     status    value
  A     A123     VN        1
  A     A234     AB        2
  B     B123     VN        3
  B     B321     AB        4
  C     C324     AB        5
  </code></pre>
  
  <p>I want to get the id1, id2 whose status is VN and the value to be the max for those records who share the same id1. </p>
  
  <p>In this case, the output should look like</p>
  
  <pre><code>id1    id2     status    value
  A     A123     VN        2
  B     B123     VN        4
  </code></pre>
  
  <p>I am not quite sure how to write the query and any help would be appreciated! </p>
  
  <p>Here is what I have done so far. </p>
  
  <pre><code>select
      test1.*
  from test1 
  inner join
  (select 
      part_id 
  from table1
  where status = 'VN') a
  on test1.id1 = a.id1;
  </code></pre>
  
  <p>And I could get this but I don't know how to correctly do the group and max(value):</p>
  
  <pre><code>id1    id2     status    value
  A     A123     VN        1
  A     A234     AB        2
  B     B123     VN        3
  B     B321     AB        4
  </code></pre>