title: How to find the record in a table that contains the maximum value?
body: <p>Although this question looks simple, it is kind of tricky.</p>
  <p>Consider the following table:</p>
  <pre><code>CREATE TABLE A (
    id INT,
    value FLOAT,
    &quot;date&quot; DATETIME,
    group VARCHAR(50) 
  );
  </code></pre>
  <p>I would like to obtain the <code>ID</code> and <code>value</code> of the records that contain the maximum <code>date</code> grouped by the column <code>group</code>. In other words, something like <em>&quot;what is the newest value for each group?&quot;</em> What query will answer that question?</p>
  <p>I can get each group and its maximum date:</p>
  <pre><code>SELECT group, MAX(date) 
    FROM A 
    GROUP BY group; -- I also need the &quot;ID&quot; and &quot;value&quot;`
  </code></pre>
  <p>But I would like to have the &quot;ID&quot; and value of the record with the highest date.</p>
  <p>Making a <code>JOIN</code> between <code>A</code> and the result could be the answer, but there is no way of knowing which record <code>MAX(date)</code> refers to (in case the <code>date</code> repeats).</p>
  <p>Sample data:</p>
  <pre><code>INSERT INTO A
    VALUES
  (1, 1.0, '2000-01-01', 'A'),
  (2, 2.0, '2000-01-02', 'A'),
  (3, 3.0, '2000-01-01', 'B'),
  (4, 2.0, '2000-01-02', 'B'),
  (5, 1.0, '2000-01-02', 'B')
  ;
  </code></pre>