title: MySQL: Sophisticated GROUP BY query
body: <p>We are trying to find a solution to a problem that I have been able to replicate with an illustrative table.  Here's how the illustrative table is created:</p>
  
  <pre><code>create table illustrativeTable
  (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    label VARCHAR(4),
    reportingDate DATE,
    attr_1 INT,
    attr_2 INT,
    attr_3 INT,
  
    PRIMARY KEY(id)
  );
  </code></pre>
  
  <p>I have populated the illustrative table as follows:</p>
  
  <pre><code>INSERT INTO illustrativeTable(label, reportingDate, attr_1, attr_2, attr_3) VALUES('A', '2018-01-01', '1', '3', '100'),
  ('A', '2018-01-05', '2', '4', '125'), ('A', '2018-01-07', '2', '5', '125'),
  ('A', '2018-01-08', '3', '6', '150'), ('A', '2018-01-11', '4', '7', NULL),
  ('B', '2018-01-02', '1', '3', '50'), ('B', '2018-01-05', '2', '5', '75'),
  ('B', '2018-01-06', '3', '6', '200'), ('B', '2018-01-16', '3', '5', '200'),
  ('C', '2018-01-05', '6', '9', '175'),('C', '2018-01-08', '7', '9', '225'),
  ('D', '2018-01-01', '2', '5', '55'), ('D', '2018-01-15', '3', '6', 85),
  ('D', '2018-01-21', '4', '7', '75'), ('E', '2018-01-25', '2', '4', '65'),
  ('E', '2018-01-28', '2', '5', NULL);
  </code></pre>
  
  <p>The query</p>
  
  <pre><code>SELECT * FROM illustrativeTable;
  </code></pre>
  
  <p>yields the following output:</p>
  
  <pre><code>+----+-------+---------------+--------+--------+--------+
  | id | label | reportingDate | attr_1 | attr_2 | attr_3 |
  +----+-------+---------------+--------+--------+--------+
  |  1 | A     | 2018-01-01    |      1 |      3 |    100 |
  |  2 | A     | 2018-01-05    |      2 |      4 |    125 |
  |  3 | A     | 2018-01-07    |      2 |      5 |    125 |
  |  4 | A     | 2018-01-08    |      3 |      6 |    150 |
  |  5 | A     | 2018-01-11    |      4 |      7 |   NULL |
  |  6 | B     | 2018-01-02    |      1 |      3 |     50 |
  |  7 | B     | 2018-01-05    |      2 |      5 |     75 |
  |  8 | B     | 2018-01-06    |      3 |      6 |    200 |
  |  9 | B     | 2018-01-16    |      3 |      5 |    200 |
  | 10 | C     | 2018-01-05    |      6 |      9 |    175 |
  | 11 | C     | 2018-01-08    |      7 |      9 |    225 |
  | 12 | D     | 2018-01-01    |      2 |      5 |     55 |
  | 13 | D     | 2018-01-15    |      3 |      6 |     85 |
  | 14 | D     | 2018-01-21    |      4 |      7 |     75 |
  | 15 | E     | 2018-01-25    |      2 |      4 |     65 |
  | 16 | E     | 2018-01-28    |      2 |      5 |   NULL |
  +----+-------+---------------+--------+--------+--------+
  </code></pre>
  
  <p>Our issue is that we want to retrieve label, reportingDate and attr_3, <strong>from selected tuples</strong>, with the following constraints:<br>
  1) attr_2 - attr_1 = 3<br>
  2) attr_3 IS NOT NULL<br>
  3) In case of multiple hits, the value with the <strong>highest value for reportingDate</strong> is selected  </p>
  
  <p>The simplistic query:</p>
  
  <pre><code>SELECT label, reportingDate, attr_3 FROM illustrativeTable
  WHERE label IN ('A', 'B', 'C', 'E') AND (attr_2-attr_1=3)
  AND attr_3 IS NOT NULL GROUP BY label;
  </code></pre>
  
  <p>yields the following result:</p>
  
  <pre><code>+-------+---------------+--------+
  | label | reportingDate | attr_3 |
  +-------+---------------+--------+
  | A     | 2018-01-07    |    125 |
  | B     | 2018-01-05    |     75 |
  | C     | 2018-01-05    |    175 |
  +-------+---------------+--------+
  </code></pre>
  
  <p>The problem with this result is that for label 'A' the highest reportingDate that meets all the constraints is 2018-01-08.  Similarly for label 'B' the highest reportingDate that meets all the constraints is 2018-01-06.</p>
  
  <p>We would like to tweak the query so that the output looks as follows:</p>
  
  <pre><code>+-------+---------------+--------+
  | label | reportingDate | attr_3 |
  +-------+---------------+--------+
  | A     | 2018-01-08    |    150 |
  | B     | 2018-01-06    |    200 |
  | C     | 2018-01-05    |    175 |
  +-------+---------------+--------+
  </code></pre>
  
  <p>I did try some ideas from 
  <a href="https://paulund.co.uk/get-last-record-in-each-mysql-group" rel="nofollow noreferrer">https://paulund.co.uk/get-last-record-in-each-mysql-group</a>
    but I could not get the results I am looking for.</p>