List each user’s last message

Question

Does anyone know how I could list each user’s last message?

I was able to do that, but it lists the last message from just one user, and I wanted it to list everyone who sent a message to the user in question (code 3).

SELECT m1.codigo,
       m1.codigo_remetente,
       usuario.url,
       m1.descricao,
       m1.data,
       m1.visualizado,
       usuario.nome,
       usuario.sobrenome
  FROM mensagens m1
  JOIN usuario ON m1.codigo_remetente = usuario.codigo
   AND m1.codigo_destinatario = 3
   AND data = (SELECT MAX(data)
                 FROM mensagens m2
                WHERE m2.codigo_remetente = m1.codigo_remetente)
 ORDER BY data DESC

Table User: code, name, surname, url.

Table Message: code, sender code, destination code, description, date

Answer 1

You can use the clause NOT EXISTS to specify that you want the last message sent to the code user 3, as in the case of any user, as long as there is no message sent later:

SELECT men.codigo,
       men.data,
       men.descricao,
       men.visualizado,
       men.codigo_remetente,
       usu.url,
       usu.nome,
       usu.sobrenome
  FROM mensagem men
       INNER JOIN usuario usu ON men.codigo_remetente = usu.codigo
 WHERE men.codigo_destinatario = 3
   AND NOT EXISTS(SELECT 1
                    FROM mensagem men2
                   WHERE men2.codigo_remetente = men.codigo_remetente
                     AND men2.codigo_destinatario = men.codigo_destinatario
                     AND men2.data > men.data)
 ORDER BY men.data DESC

Subqueries with EXISTS or NOT EXISTS

If a subquery Returns any Rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

Or in free translation:

If the subquery returns any line, EXISTS will BE TRUE, and NOT EXISTS will BE FALSE

---

I would like to make an extra remark:

Although, in theory, ON is the same in terms of performance to be used WHERE, it is "socially" more acceptable that you use the clause ON of JOIN only to link the tables. To restrict results use WHERE leaving his query more readable.