title: MySQL: Group by query optimization
body: <p>I've got a table of the following schema:</p>
  
  <pre><code>+----+--------+----------------------------+----------------------------+
  | id | amount | created_timestamp          | updated_timestamp          |
  +----+--------+----------------------------+----------------------------+
  | 1  |   1.00 | 2018-01-09 12:42:38.973222 | 2018-01-09 12:42:38.973222 |
  +----+--------+----------------------------+----------------------------+
  </code></pre>
  
  <p>Here, for <code>id = 1</code>, there could be multiple amount entries. I want to extract the last added entry and its corresponding amount, grouped by id.</p>
  
  <p>I've written a working query with an inner join on the self table as below:</p>
  
  <pre><code>SELECT t1.id, 
         t1.amount, 
         t1.created_timestamp, 
         t1.updated_timestamp 
  FROM   transactions AS t1 
         INNER JOIN (SELECT id, 
                            Max(updated_timestamp) AS last_transaction_time 
                     FROM   transactions 
                     GROUP  BY id) AS latest_transactions 
                 ON latest_transactions.id = t1.id 
                    AND latest_transactions.last_transaction_time = 
                        t1.updated_timestamp; 
  </code></pre>
  
  <p>I think inner join is an overkill and this can be replaced with a more optimized/efficient query. I've written the following query with <code>where</code>, <code>group by</code>, and <code>having</code> but it isn't working. Can anyone help?</p>
  
  <pre><code>select id, any_value(`updated_timestamp`), any_value(amount) from transactions group by `id` having max(`updated_timestamp`);
  </code></pre>