MySQL SELECT with ORDER BY in GROUP BY

Question

MySQL SELECT with ORDER BY in GROUP BY

Navigation

#1 by (1 votes)

0

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

In doubt, I need to SELECT contacts, and in LEFT JOIN only show the last status recorded in the status table. I did this:

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

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

php mysql group-by order-by

asked by anonymous 13.06.2018 / 13:31

1 answer

Problem returning a query read txt files with php

score 1 · Accepted Answer

I do not know if the status table has a id field; if you have, follow

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