左联接+排序依据,然后分组依据[重复]

我有两张桌子:公司和电子邮件。

我想选择每家公司的最后一封电子邮件

我试过这个:

SELECT * 
  FROM companies 
  LEFT 
  JOIN emails 
    ON companies.company_id = emails.company_id 
 WHERE companies.status = 'active' 
 GROUP 
    BY emails.company_id 
 ORDER  
    BY emails.date DESC;

但这行不通,按部分排序应该在某种程度上先于按部分分组,因为我想得到每家公司的最后一封电子邮件,而不是对最终结果排序。

有人能帮我吗?

谢谢

使现代化

我最终做到了:

SELECT * 
  FROM companies 
  LEFT 
  JOIN (
       SELECT company_id, date
         FROM emails 
        ORDER
           BY date DESC
       ) emails
     ON companies.company_id = emails.company_id 
  WHERE companies.status = 'active' 
  GROUP 
     BY emails.company_id;

谢谢你的帮助。