php - how to select latest record from table by using Group by statements -

i fetch chat messages table , group according sender id

---

messages table

msg_id     msg(messages)  sender(user id)  receiver (user id)  time (timestamp) ------------------------------------------------------------------------------  1              hello bro             1              2                 12am  2              hello                 2              1                 12am  3              disscuss  1              2                 12:01am  4              free          1              2                 12:03am  ----------------------------------------------------- **user table** -----------------------------------------------------  u_id       user_name       1           khalid   2           brain   3           abdullah 

then when make query

select sender.user_name sender, receiver.user_name receiver,        messages.time msgtime, messages.msg msg,sender.u_id u_id,        sender.user_name user_name messages left join user         sender on messages.sender = sender.u_id left join user        receiver on messages.receiver = receiver.u_id         sender.u_id<>1 group messages.sender order         messages.msg_id desc 

but when use group sender not show latest message in group statement show old record

what want

i want query show latest last message of every sender

you can without grouping @ - left joining messages table itself, predicate being same sender, , later timestamp. if there no later timestamp, end null values in second table, meaning you've identified recent message.

select s.user_name `from`, r.user_name `to`, m1.msg, m1.time    messages m1     left join messages m2       on m1.time < m2.time , m1.sender = m2.sender     inner join users s       on m1.sender = s.u_id     inner join users r       on m1.receiver = r.u_id   m2.sender null; 

if absolutely want use group by, can first finding max(time) each sender, , joining result messages , users table, so:

select s.user_name `from`, r.user_name `to`, m.msg, m.time   messages m     inner join users s       on m.sender = s.u_id     inner join users r       on m.receiver = r.u_id     inner join (       select sender, max(`time`) ts         messages         group sender     ) q on m.sender = q.sender , m.time = q.ts 

both queries give identical results