找不到每个会话的列表消息的查询

I have the following table. I want to get the last message of each conversation. Please help with a proper query to get it.

  ID   ID2   User1   User2           Message                 TimeStamp         
 ---- ----- ------- ------- ------------------------- ------------------------ 
   1     1       1       2   hii                       March, 04 2017 8:30:00  
   1     2       2       1   hey bro                   March, 04 2017 8:31:00  
   1     3       1       2   hw are u?                 March, 04 2017 8:32:00  
   1     4       2       1   good,thnks4asking         March, 04 2017 8:33:00  
   5     1       3       1   r u there?                March, 04 2017 8:34:00  
   5     2       1       3   yup,say                   March, 04 2017 8:35:00  
   5     3       3       1   hw are u?                 March, 04 2017 8:36:00  
   5     4       1       3   m fine                    March, 04 2017 8:37:00  
   5     5       3       1   where are u these days?   March, 04 2017 8:38:00  
   1     5       1       2   your most welcom :D       March, 04 2017 8:39:00

Here ID is the discussion id (chat id say for example 1 is the discussion id between users having id 1 and 2 and 5 is the for discussion between users having id 1 and 3 ) and ID2 is the number in the discussion. The resultant should show 2 messages because "your most welcome" is the last message between users having ID 1 and 2 and the 2nd message "where are u these days?" is the last message between users having ID 1 and 3

I tried many queries like

 SELECT message ,MAX(id2) 
 FROM pm 
 WHERE user1=1 OR user2=1 
 GROUP BY id

 SELECT message 
 FROM pm 
 WHERE user1=1 OR user2=1 
 GROUP BY id 
 HAVING id2=MAX(id2)

NOTE: For now I just want the last from the user (having ID=1) point of view.

This might be help you:

    select * from pm where user1 = 1 order by id desc limit 1;

Here 'id' (you can use according to your need i.e timestamp) is your column name on basis which your will get descending order with limit 1 so that you will get your last message.

There is more than one way to do this. You are on the right track. I think you just need to use a JOIN.

The following example is a SELF JOIN. The first query is the most important. It is similar to the ones you have already written. It uses a GROUP BY clause. It returns only two rows. Those rows represent the last message timestamp of each conversation. We then join those two rows back to the main table to get the message. We join ON id AND maximum Timestamp.

SELECT *
FROM (
  /* Get the latest message timestamp for each conversation. */
  SELECT ID, MAX(TimeStamp) AS MaxTimeStamp
  FROM Conversations
  WHERE User1 = 1 OR User2 = 1
  GROUP BY ID
) AS A
LEFT JOIN (
  /* Get the message */
  SELECT ID, Message, TimeStamp
  FROM Conversations
) AS B
ON A.ID = B.ID
AND A.MaxTimeStamp = B.TimeStamp

The SQL above gets the results you want. It gets us

The last message of each conversation that User1 has had with anyone

Note that because your table ONLY has conversations containing User1, the WHERE clause in the first query is entirely optional. You'll get the same result set if it is removed. Also - because it looks like ID2 is auto incrementing in an ascending fashion - you could change MAX(TimeStamp) out for MAX(ID2) and get the same result set. Like I said, more than one way.

Hope this helps!

Example SQL Fiddle Here. The rows you want are below.