CODEWITHSUNDEEP
phpmysqlsql
Developer
Developer

Reputation: 385

MySql SQL Query of Group by and Order by

I have a table in mysql. Which structure is :

id   sender   receiver        time              message
-------------------------------------------------------
1     23         34      2014-11-02 14:55:02      1
2     22         34      2014-11-02 16:55:02      2
3     21         35      2014-11-02 16:55:02      5
4     23         34      2014-11-02 16:55:02      2
5     22         35      2014-11-02 16:55:02      1

Now i need to make a query that will fetch the latest message of a particular receiver from a particular sender if the communication is more than one.Otherwise it give the single row . Here i want to make a query with receiver id = 34 and it shouldgive me 4 th row and 2nd row. i.e.

2     22         34      2014-11-02 16:55:02      2
4     23         34      2014-11-02 16:55:02      2

Here i am not asking a particular number of row from the db. This is a conversation db. And i want the conversation list of a user. And when there is more than one row for the same sender and receiver , then i want the latest conversation.

How to do that?

Upvotes: 0

Views: 105

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

There are many ways to achieve it and one way is to use left join then with the join condition group the data and finally the additional condition for further filter

select
m.* from messages m
left join messages m1 on m1.receiver = m.receiver
and m.id < m1.id
and m.sender = m1.sender
where m1.id is null
and m.receiver = 34;

Other way is to use exists as an alternative for group by

select m.* from messages m
where not exists
(
  select 1 from messages m1
  where
  m1.receiver = m.receiver
  and m.id < m1.id
  and m.sender = m1.sender
)
and m.receiver = 34;

demo

Upvotes: 1

radar
radar

Reputation: 13425

You can get latest message time using group by and then use it in subquery to get all message details

 Select * from
 table1 T
 Join(
      Select sender ,max(time) as lastTime
      From table1
      Where receiver = 34
     Group by sender) T2
 On T.sender = T2.sender
 and T.time = T2.lastTime 
 And T.receiver = 34

Upvotes: 1

Related Questions