MySQL LIMIT within JOIN

Question

I have an 1:m association.

symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created. I need to join latest symbols with company_key_statistics, but I need only latest company_key_statistics. For example I need to get ORCL and MSFT symbols and only their latest company_key_statistics.

So far I have tried this.

SELECT `symbols`.`id`, 
       `symbols`.`symbol`, 
       `statistics`.
       `marketCapitalization` 
FROM   `symbols` 
       LEFT JOIN (SELECT `s`.`companyId`, 
                         `s`.`marketCapitalization` 
                  FROM   `company_key_statistics` AS `s`
                  WHERE  `s`.`companyId` = `symbols`.`id` 
                  ORDER by `createdAt` 
                  DESC LIMIT 1) AS `statistics` 
ON     `symbols`.`id` = `statistics`.`companyId` 
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

But unfortunately I found that I can't use columns from parent query in JOIN subqueries.

How can I achieve to this?

score 4 · Answer 1 · edited May 23 '17 at 12:40

For a correlated subquery you should use a CROSS APPLY, unfortunately MySql doesn't allow it, but you can simulate it on this way:

SELECT     `symbols`.`id`, 
           `symbols`.`symbol`, 
           `statistics`.
           `marketCapitalization`,
           `s`.`companyId`,
           `s`.`marketCapitalization`  
FROM       `symbols` 
INNER JOIN `company_key_statistics` `s`
ON         `s`.`pk` = (SELECT `pk`          -- the PK of company_key_statistics
                       FROM   `company_key_statistics`
                       WHERE  `companyId` = `symbols`.`id` 
                       ORDER by `createdAt` 
                       DESC LIMIT 1) 
WHERE  `symbols`.`symbol` IN ('ORCL', 'SNAP');

You can find some example on SO.

score 2 · Accepted Answer · edited May 23 '17 at 12:40

Try this:

SELECT sym.id, sym.symbol, s.marketCapitalization 
  FROM symbols AS sym 
  INNER JOIN company_key_statistics AS s 
     ON sym.id = s.companyId 
  INNER JOIN (SELECT companyId, MAX(createdAt) AS createdAt 
                FROM company_key_statistics 
                GROUP BY companyId) AS smax 
     ON smax.companyId = s.companyId AND smax.createdAt = s.createdAt 
  WHERE sym.symbol IN ('ORCL','SNAP');

Since your posted example was a LEFT JOIN, maybe you want also companies without market capitalization data in your answer. In this case, we have to nest the subselects, in this way:

SELECT sym.id, sym.symbol, s.marketCapitalization 
  FROM symbols AS sym 
  LEFT JOIN (SELECT * 
               FROM company_key_statistics
               INNER JOIN (SELECT companyId, MAX(createdAt) AS createdAt 
                             FROM company_key_statistics 
                             GROUP BY companyId) AS smax 
               USING (companyId, createdAt)) AS s 
    ON sym.id = s.companyId 
 WHERE sym.symbol IN ('ORCL','SNAP');

The general problem we are addressing here is called groupwise maximum and many SO and DBA.SE answers deal with it. Check this one for instance.

MySQL LIMIT within JOIN

2 Answers2