title: get max ids by group mysql
body: <p>I have this table called <code>sw_sowing</code></p>
  
  <pre><code>-----------------------------------------------------------------
   id  |   id_unit  |  date      |  id_variety |  type  |  status |
  -----------------------------------------------------------------
    1  |      1     | 2017-08-10 |     1        |  SW   |    200  |
  -----------------------------------------------------------------
    2  |      1     | 2017-10-10 |     1        |  ER   |    100  |
  -----------------------------------------------------------------
    3  |      1     | 2017-11-30 |     2        |  SW   |    100  |
  -----------------------------------------------------------------
    4  |      2     | 2017-12-10 |     3        |  SW   |    200  |
  -----------------------------------------------------------------
    5  |      2     | 2017-12-10 |     3        |  ER   |    100  |
  -----------------------------------------------------------------
    6  |      3     | 2017-08-05 |     4        |  SW   |    200  |
  -----------------------------------------------------------------
    7  |      3     | 2017-12-13 |     4        |  ER   |    100  |
  -----------------------------------------------------------------
    8  |      3     | 2018-01-04 |     1        |  SW   |    100  |
  -----------------------------------------------------------------
    9  |      3     | 2018-01-04 |     2        |  SW   |    100  |
  -----------------------------------------------------------------
  </code></pre>
  
  <p>I want to know the current status and id_variety of the id_unit depending on a date.
  I have this query:</p>
  
  <pre><code>SELECT sw_sowing.id_unit, sw_sowing.id_variety, sw_sowing.type, sw_sowing.status
  FROM (
          SELECT MAX(id) AS id
          FROM sw_sowing
          WHERE date &lt;= '2018-09-06'
          GROUP BY id_unit
  ) AS sw
  INNER JOIN sw_sowing ON sw_sowing.id = sw.id
  </code></pre>
  
  <p>It gives me the following result:</p>
  
  <pre><code>----------------------------------------------------- 
     id_unit  |    id_variety |    type    |   status |
  -----------------------------------------------------
        1     |        2      |     SW     |    100   |
  -----------------------------------------------------
        2     |        3      |     ER     |    100   |
  -----------------------------------------------------
        3     |        2      |     SW     |    100   |
  -----------------------------------------------------
  </code></pre>
  
  <p>The problem is that the <code>id_unit = 3</code> has two different <code>id_variety</code> with <code>state = 100</code>, but the previous query give the last one and I want the two <code>id_variety</code>.</p>
  
  <p>Something like this:</p>
  
  <pre><code>----------------------------------------------------- 
     id_unit  |    id_variety |    type    |   status |
  -----------------------------------------------------
        1     |        2      |     SW     |    100   |
  -----------------------------------------------------
        2     |        3      |     ER     |    100   |
  -----------------------------------------------------
        3     |        1      |     SW     |    100   |
  -----------------------------------------------------
        3     |        2      |     SW     |    100   |
  -----------------------------------------------------
  </code></pre>
  
  <p>I hope that you can help me!</p>