CODEWITHSUNDEEP
phpmysqlsqlselect
Youssef
Youssef

Reputation: 421

MySQL statement need - Select recent message from each user

I'm working on MySQL and here's my data :

Tables :

User : 

+----+----------+
+ id + username +
+----+----------+

Messages : 

+----+------+----+---------+---------------+
+ id + from + to + message + date_creation +
+----+------+----+---------+---------------+

Need : I want to retrieve (one) the last message sent by every user to user id '20' (for instance)

I tried : 

SELECT u.username
     , m.id, u.lastActivity
     , LEFT( m.message, 60 ) AS message
     , m.lu
     , m.date_envoi 
  FROM messages m
     , user u 
 WHERE m.to_id = 1 
   AND u.id = m.from_id 
 GROUP 
    BY u.username 
 ORDER 
    BY m.date_envoi DESC

Upvotes: 0

Views: 357

Answers (1)

peter.petrov
peter.petrov

Reputation: 39477

Try this query.

select m1.* 
from
`Messages` m1
join 
(
    select m.`from`, max(m.date_creation) as date_creation 
    from
    `Messages` m
    where m.to = 20
    group by m.`from`   
) t on m1.`from` = t.`from` and m1.date_creation = t.date_creation

Upvotes: 1

Related Questions