title: MySQL query with join on derived table containing group by, return most recent related record
body: <p>Hi everyone<br>
  I've got a MySQL query which uses joins on a couple of derived tables. One of the derived queries works great, and I'm getting the correct number of rows returned.<br>
  But I'm also trying to display some additional info from a related table for each row and this is where I'm struggling. I've read several other questions on here with similar questions but I can't manage to apply it to my query.</p>
  
  <p>Here's the query:</p>
  
  <pre><code>SELECT `adbookings`.`company_id`, `companies`.`company_name`, `adbookings`.`run`, `adbookings`.`bill_freq`, `adbookings`.`currency`, FORMAT(`adinserts`.`rate`, 2) AS `rate`, `publications`.`title`, CONCAT(FLOOR(CAST(UNIX_TIMESTAMP()-`invoices`.`last_invoice_stamp` AS SIGNED INTEGER)/86400), ' days ago') AS `last_invoice_days_ago`, `invoices`.`last_invoice_no`, `invoices`.`last_invoice_reference`
  FROM `adinserts` 
  INNER JOIN 
  (
  SELECT `publications`.`publication_id`, `publications`.`art_stamp`, `publications`.`title`
  FROM `publications`
  LEFT JOIN `adinserts` ON `publications`.`publication_id`=`adinserts`.`publication_id` AND `adinserts`.`invoice_id` IS NOT NULL
  WHERE `publications`.`publication_stamp`>=UNIX_TIMESTAMP('2010-01-01 00:00:00')
  GROUP BY `publications`.`publication_id`
  HAVING COUNT(`adinserts`.`invoice_id`)>0
  ) AS `publications` ON `adinserts`.`publication_id`=`publications`.`publication_id`
  LEFT JOIN `adbookings` ON `adinserts`.`booking_id`=`adbookings`.`booking_id` 
  LEFT JOIN
  (
  SELECT `company_id`, CONCAT_WS('', `prefix`, `invoice_id`, `suffix`) AS `last_invoice_no`, MAX(`invoices`.`invoice_stamp`) AS `last_invoice_stamp`, `reference` AS `last_invoice_reference` 
  FROM `invoices`
  GROUP BY `invoices`.`company_id`
  ) AS `invoices` ON `adbookings`.`company_id`=`invoices`.`company_id`
  LEFT JOIN `companies` ON `adbookings`.`company_id`=`companies`.`company_id` 
  WHERE `adinserts`.`invoice_id` IS NULL AND `adinserts`.`cancel_stamp` IS NULL AND `adinserts`.`rate`>0
  ORDER BY `publications`.`art_stamp`, `companies`.`company_name`</code></pre>
  
  <p>My problem is with the second derived table... <code>LEFT JOIN (...) AS invoices</code></p>
  
  <p>I'm trying to get the invoice_stamp of the most recent related invoice for that company, using MAX(invoice_stamp). That appears to work and does return the related record with the largest value of invoice_stamp (a Unix timestamp).</p>
  
  <p>But I'm also trying to get the matching invoice_id and reference fields of that record returned by the MAX() aggregate function. But the invoice_id and reference fields do not match the record returned by the MAX() function.</p>
  
  <p>Anyone know how I can get the full details of the record returned by the aggregate function in the derived table?</p>