title: Can query be optimized: Get a records max date then join the max date's values
body: <p>I've created a query that returns the results I want but I feel there must be a better way to do this. Any guidance would be appreciated.</p>
  
  <p>I am trying to get all items for a specific meeting and join their max meeting date &lt; X and join the max date's committee acronym. X is the current meeting date.</p>
  
  <p>I've tried a few different queries but none, other than the one below, returned the expected results all the time. </p>
  
  <p>You can see this query in action by going to <a href="http://rextester.com/FIQQ26849" rel="nofollow noreferrer">rextester</a>.</p>
  
  <pre><code>DROP TABLE IF EXISTS `committees`;
  CREATE TABLE committees
      (`id` int, `acronym` varchar(4))
  ;
  
  INSERT INTO committees
      (`id`, `acronym`)
  VALUES
      (1, 'Com1'),
      (2, 'Com2'),
      (3, 'Com3')
  ;
  
  DROP TABLE IF EXISTS `meetings`;
  CREATE TABLE meetings
      (`id` int, `date` datetime, `committee_id` int)
  ;
  
  INSERT INTO meetings
      (`id`, `date`, `committee_id`)
  VALUES
      (1, '2017-01-01 00:00:00', 1),
      (2, '2017-02-02 00:00:00', 2),
      (3, '2017-03-03 00:00:00', 2)
  ;
  
  DROP TABLE IF EXISTS `agenda_items`;
  CREATE TABLE agenda_items
      (`id` int, `name` varchar(6))
  ;
  
  INSERT INTO agenda_items
      (`id`, `name`)
  VALUES
      (1, 'Item 1'),
      (2, 'Item 2'),
      (3, 'Item 3')
  ;
  
  DROP TABLE IF EXISTS `join_agenda_items_meetings`;
  CREATE TABLE join_agenda_items_meetings
      (`id` int, `agenda_item_id` int, `meeting_id` int)
  ;
  
  INSERT INTO join_agenda_items_meetings
      (`id`, `agenda_item_id`, `meeting_id`)
  VALUES
      (1, 1, 1),
      (2, 1, 2),
      (3, 2, 1),
      (4, 3, 2),
      (5, 2, 1),
      (6, 1, 3)
  ;
  
  
  
  
  SELECT agenda_items.id, 
         meetings.id, 
         meetings.date, 
         sub_one.max_date, 
         sub_two.acronym 
  FROM   agenda_items 
         LEFT JOIN (SELECT ai.id                AS ai_id, 
                           me.id                AS me_id, 
                           Max(me.date) AS max_date 
                    FROM   agenda_items AS ai 
                           JOIN join_agenda_items_meetings AS jaim 
                             ON jaim.agenda_item_id = ai.id 
                           JOIN meetings AS me 
                             ON me.id = jaim.meeting_id 
                    WHERE  me.date &lt; '2017-02-02' 
                    GROUP  BY ai_id) sub_one 
                ON sub_one.ai_id = agenda_items.id 
         LEFT JOIN (SELECT agenda_items.id       AS age_id, 
                           meetings.date AS meet_date, 
                           committees.acronym    AS acronym 
                    FROM   agenda_items 
                           JOIN join_agenda_items_meetings 
                             ON join_agenda_items_meetings.agenda_item_id = agenda_items.id 
                           JOIN meetings 
                             ON meetings.id = join_agenda_items_meetings.meeting_id 
                           JOIN committees 
                             ON committees.id = meetings.committee_id 
                    WHERE  meetings.date) sub_two 
                ON sub_two.age_id = agenda_items.id 
                   AND sub_one.max_date = sub_two.meet_date 
         JOIN join_agenda_items_meetings 
           ON agenda_items.id = join_agenda_items_meetings.agenda_item_id 
         JOIN meetings 
           ON meetings.id = join_agenda_items_meetings.meeting_id 
  WHERE  meetings.id = 2;
  </code></pre>
  
  <p><strong><em>REVIEW / TESTING OF ANSWERS (REVISED</em>):*</strong></p>
  
  <p><strong>I've revised the testing based on the comments made.</strong></p>
  
  <p>Since I put a bounty on this question I felt I should show how I'm evaluating the answers and give some feedback. Overall I'm very grateful to all how have helped out, thank you.</p>
  
  <p>For testing, I reviewed the queries against:</p>
  
  <ul>
  <li>the <a href="http://rextester.com/FIQQ26849" rel="nofollow noreferrer">initial rextester</a> </li>
  <li>a <a href="http://rextester.com/ENCPUR72665" rel="nofollow noreferrer">modified version of the initial rextester</a> with all 4 queries for 2 separate datasets</li>
  <li>a larger data set from my actual database</li>
  </ul>
  
  <p><strong>My Original Query with EXPLAIN</strong></p>
  
  <pre><code>+----+-------------+---------------------------+------+----------------------------------------------+
  | id | select_type | table                     | rows | Extra                                        |
  +----+-------------+---------------------------+------+----------------------------------------------+
  |  1 | PRIMARY     | meetings                  |    1 |                                              |
  |  1 | PRIMARY     | join_agenda_item_meetings | 1976 | Using where; Using index                     |
  |  1 | PRIMARY     | agenda_items              |    1 | Using index                                  |
  |  1 | PRIMARY     | &lt;derived2&gt;                | 1087 |                                              |
  |  1 | PRIMARY     | &lt;derived3&gt;                | 2202 |                                              |
  |  3 | DERIVED     | join_agenda_item_meetings | 1976 | Using index                                  |
  |  3 | DERIVED     | meetings                  |    1 | Using where                                  |
  |  3 | DERIVED     | committees                |    1 |                                              |
  |  3 | DERIVED     | agenda_items              |    1 | Using index                                  |
  |  2 | DERIVED     | jaim                      | 1976 | Using index; Using temporary; Using filesort |
  |  2 | DERIVED     | me                        |    1 | Using where                                  |
  |  2 | DERIVED     | ai                        |    1 | Using index                                  |
  +----+-------------+---------------------------+------+----------------------------------------------+
  12 rows in set (0.02 sec)
  </code></pre>
  
  <p><strong>Paul Spiegel's answers.</strong> </p>
  
  <p>The <strong>initial answer works and seems to be the most efficient</strong> option presented, much more than mine.</p>
  
  <p>Paul Spiegel's first query pulls the fewest rows, is shorter and more readable than mine. It also doesn't need to reference a date which will be nicer when writing it as well.</p>
  
  <pre><code>+----+--------------------+-------+------+--------------------------+
  | id | select_type        | table | rows | Extra                    |
  +----+--------------------+-------+------+--------------------------+
  |  1 | PRIMARY            | m1    |    1 |                          |
  |  1 | PRIMARY            | am1   | 1976 | Using where; Using index |
  |  1 | PRIMARY            | am2   |    1 | Using index              |
  |  1 | PRIMARY            | m2    |    1 |                          |
  |  2 | DEPENDENT SUBQUERY | am3   |    1 | Using index              |
  |  2 | DEPENDENT SUBQUERY | m3    |    1 | Using where              |
  |  2 | DEPENDENT SUBQUERY | c3    |    1 | Using where              |
  +----+--------------------+-------+------+--------------------------+
  7 rows in set (0.00 sec)
  </code></pre>
  
  <p>This query also returns the correct results when adding <code>DISTINCT</code> to the select statement. This query does not perform as well as the first though (but it is close).</p>
  
  <pre><code>+----+-------------+------------++------+-------------------------+
  | id | select_type | table      | rows | Extra                    |
  +----+-------------+------------++------+-------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt; |    5 | Using temporary          |
  |  1 | PRIMARY     | am         |    1 | Using index              |
  |  1 | PRIMARY     | m          |    1 |                          |
  |  1 | PRIMARY     | c          |    1 | Using where              |
  |  2 | DERIVED     | m1         |    1 |                          |
  |  2 | DERIVED     | am1        | 1787 | Using where; Using index |
  |  2 | DERIVED     | am2        |    1 | Using index              |
  |  2 | DERIVED     | m2         |    1 |                          |
  +----+-------------+------------+------+--------------------------+
  8 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>Stefano Zanini's answer</strong></p>
  
  <p>This query does return the expected results using <code>DISTINCT</code>. When using <code>EXPLAIN</code> and the number of rows being pulled this query is more efficient when compared to my original one but Paul Spiegel's is just a bit better.</p>
  
  <pre><code>+----+-------------+------------+------+---------------------------------+
  | id | select_type | table      | rows | Extra                           |
  +----+-------------+------------+------+---------------------------------+
  |  1 | PRIMARY     | me         |    1 | Using temporary; Using filesort |
  |  1 | PRIMARY     | rel        | 1787 | Using where; Using index        |
  |  1 | PRIMARY     | &lt;derived2&gt; | 1087 |                                 |
  |  1 | PRIMARY     | rel2       |    1 | Using index                     |
  |  1 | PRIMARY     | me2        |    1 | Using where                     |
  |  1 | PRIMARY     | co         |    1 |                                 |
  |  2 | DERIVED     | t1         | 1787 | Using index                     |
  |  2 | DERIVED     | t2         |    1 | Using where                     |
  +----+-------------+------------+------+---------------------------------+
  8 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>EoinS' answer</strong></p>
  
  <p>As noted in the comments, this answer works if meetings are sequential, but they may not be unfortunately. </p>