title: Cannot return MAX value with specific condition
body: <p>I'm trying to get the matches of a competition of the next gameweek. A gameweek is when a match is played we can have these types of gameweeks: 20, 21, 22, 23. Each gameweek is played into a specific date. Now all the matches have a specific status, I can see if a match of a particular gameweek was played if the status value is 5, instead if is not played can have as status: 1, 2, 3, 4.</p>
  
  <p>My query have this design:</p>
  
  <pre><code> SELECT m.id, m.round_id, m.datetime,
    CASE m.status
      WHEN 1 THEN 'scheduled'
      WHEN 2 THEN 'postponed'
      WHEN 3 THEN 'canceled'
      WHEN 4 THEN 'playing'
      WHEN 5 THEN 'finished'
    END AS match_status,
    m.gameweek, m.home_team_id, m.home_team_half_time_score, m.home_team_score,
    m.home_extra_time, m.home_penalties, m.away_team_id, m.away_team_half_time_score,
    m.away_team_score, m.away_extra_time, m.away_penalties, m.venue_id,
    m.venue_attendance, m.aggregate_match_id,
    t.name AS home_team_name,
    t_info.shield_link AS home_team_shield,
    t2.name AS away_team_name,
    t2_info.shield_link AS away_team_shield,
    c.name AS competition_name,
    c.id AS competition_id,
    r.name AS round_name
    FROM `match` m
    LEFT JOIN team t ON m.home_team_id = t.id
    LEFT JOIN team_info t_info ON t.id = t_info.team_id
    LEFT JOIN team t2 ON m.away_team_id = t2.id
    LEFT JOIN team_info t2_info ON t2.id = t2_info.team_id
    LEFT JOIN competition_rounds r ON m.round_id = r.id
    LEFT JOIN competition_seasons s ON r.season_id = s.id
    LEFT JOIN competition c ON c.id = s.competition_id
    WHERE 1 AND m.status IN (1, 2, 3, 4, 5) AND m.round_id IN (10, 488, 392, 70)  
    AND m.gameweek = (SELECT MAX(m2.gameweek)
    FROM `match` m2 WHERE m2.round_id = m.round_id AND m2.status = m.status) 
    ORDER BY m.datetime DESC LIMIT 10 
  </code></pre>
  
  <p><strong>Main Problem and Data structure</strong></p>
  
  <p>main problem: the gameweek. I want return the next gameweek of the match already played, so suppose I have the following matches:</p>
  
  <pre><code>| id | round.id | status | gameweek | date
    1      488        5         1        1/07/2018
    2      488        5         1        1/07/2018
    3      488        5         1        1/07/2018
    4      488        1         2        7/07/2018
    5      488        1         2        7/07/2018
    6      488        1         2        7/07/2018
    7      488        1         3        16/07/2018
    8      488        1         3        16/07/2018
    9      488        1         3        16/07/2018
  </code></pre>
  
  <p>my query will return the matches id: 7, 8, 9. But should return instead: 4, 5, 6 because is the next gameweek that need to be played, the gameweek 1 has totally played.</p>
  
  <p>Noticed that if there are almost one match that isn't played yet in the gameweek 1 for example, the query should return the gameweek 1 not the 2.</p>
  
  <p>How can I fix this?</p>
  
  <p>Thanks.</p>
  
  <p><strong>Additional further details</strong></p>
  
  <p>in the first part of the query I select only the fields that I want return from the table joined. The selection is made using the main table match where all the matches are stored. I have the the join on the table team and team_info which takes the information of the two teams that play the match.</p>
  
  <p>After this, I join the competition_rounds which get the details of the round, the round is simply an organization of the match, the same for the season, for example:</p>
  
  <pre><code>round.name = Regular Season | season.id = 5 season.name = 2017/2018 | id = 5
  </code></pre>
  
  <p>this doesn't really matter in this question but I'm trying to explain all the query activities.</p>
  
  <p>In the where clause I get all the matches which have as status all the status available, that are: 1, 2, 3, 4, 5 (you can find the status description in the query fields selector case). Later I have also a filter that return all the matches that are part of these rounds: 10, 488, 392, 70.</p>