List the last message of each user

Question

List the last message of each user

Navigation

#1 by (1 votes)

1

Does anyone know how I could list the last message of each user?

I was able to do this but list the last message of only one user, and I wanted it to list everyone who sent the 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

User table: code, name, surname, url.

Message table: code, repository_code, diskette_code, description, date

mysql query select

asked by anonymous 27.05.2017 / 04:20

1 answer

Problems accessing multisite wordpress website panel Calculate average of the value of registrations up to 100km sql

score 1 · Accepted Answer

You can use the NOT EXISTS clause to specify that either the last message sent to the code user 3, as in the example, by 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 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 leave one extra note here:

While theoretically using ON is equal in performance to WHERE , it is "socially" more acceptable that you use the ON clause of JOIN just to bind the tables. To restrict results use WHERE making your query more readable.