LEFT JOIN + ORDER BY before GROUP BY

Question

I have two tables: companies and emails.

I would like to select the last email of every company

I tried this:

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;

But this doesn't work, the ORDER BY part should somehow precede the GROUP BY part, because I want to get the last email of every company, not sort the end results.

Can someone help me?

Thanks.

UPDATE

I ended up doing this:

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;

Thank you for the help.

Why not use a subquery to get the last email for each company? — Bart, May 03 '18 at 14:53
Can there be ties, i.e. two emails for one company with the same emails.date? — Thorsten Kettner, May 03 '18 at 15:12

score 1 · Accepted Answer · edited May 03 '18 at 15:45

1

If I understand your problem the right way, I think something like that could work.

SELECT * FROM companies c 
LEFT JOIN emails e ON c.company_id = e.company_id
AND (e.company_id, e.date) IN (
    SELECT company_id, max(date) FROM emails GROUP BY company_id
)
WHERE c.status = 'active';

EDIT : Update with Thorsten answer

edited May 03 '18 at 15:45

Thorsten Kettner

89,309
7
49
73

answered May 03 '18 at 14:58

Henkan

310
2
10

2

What if two emails have the same (maximum) date? – Matt Raines May 03 '18 at 15:11
2

That must be `AND (e.company_id, e.date) IN (SELECT company_id, max(date) ...)`. And this condition should be in the `ON` clause, not in the `WHERE` clause, because otherwise you'd turn the outer join an inner join and remove companies without emails from the result. – Thorsten Kettner May 03 '18 at 15:16
Thank you for the help. I ended up doing this: `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;` – Zoltan May 03 '18 at 15:37
@Zoltan: That is a horrible "solution". An `ORDER BY` at the end of a subquery can be ignored by the DBMS, because table data (and a subquery is a so-called derived table) is considered unordered. Then you `group by emails.company_id` (which is null for all companies that have no email, by the way), but still `select *`. There can be multiple records per `emails.company_id` and you don't tell the DBMS which values to select. This is invalid SQL. MySQL lets this slip and returns arbitrarily picked values. So you could end up with an email date and an email text from different records even. – Thorsten Kettner May 03 '18 at 15:44
@Zoltan: Look again at Henkan's query. This is a proper query. You have accepted it (for good reason). Understand it and apply it. (If there can be multiple emails per company and date, you'll get more than one record per company with this query, but that may not be an issue for you. And it's not easy to circumvent this anyway.) – Thorsten Kettner May 03 '18 at 15:49
@Zoltan: You may want to set ONLY_FULL_GROUP_BY mode, as long as you haven't completely understood how aggregation is supposed to work. – Thorsten Kettner May 03 '18 at 15:51
_MySQL lets this slip and returns arbitrarily picked values. So you could end up with an email date and an email text from different records even_ I don't see this behavior, I checked the results, and they are correct. When would MySQL ignore ORDER BY and give arbitrarily results? – Zoltan May 03 '18 at 15:58

LEFT JOIN + ORDER BY before GROUP BY

1 Answers1