Mysql SELECT with ORDER BY no GROUP BY

Question

I have a table called contacts, all contacts made on the site are registered on it. There is also a table called status, where the service status of each is stored contact.

The question, I need to give a SELECT in the contacts, and in LEFT JOIN show only the last status recorded in the table status. I did it that way:

SELECT * FROM contato
LEFT JOIN status ON status.id_contato = contato.id
GROUP BY status.id_contato
ORDER BY contato.dia DESC

The problem in this query is that with GROUP BY it ends up showing only the first status and not the last. How to group and then show the last one (with larger and more current ID)?

Answer 1

I don’t know if the table status has a field id; if you have, follows

SELECT c.*, q.maxid
FROM contato c
LEFT JOIN (SELECT s.id_contato, MAX(s.id) as maxid
           FROM status s
           GROUP BY s.id_contato) q ON q.id_contato = c.id
ORDER BY c.dia DESC