sql query to get the latest message sent

Question

i have a chat sql table with the structure as following :

DROP TABLE IF EXISTS `chat`;
CREATE TABLE IF NOT EXISTS `chat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `idsender` int(11) DEFAULT NULL,
  `sentTime` datetime NOT NULL,
  `idreceiver` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
)

how to get the latest message sent to the user with the id = 2 from every user he has talked with (where the idreceiver = 2) ?

this is my try , it's getting me a message from every user the user with the id = 2 has talked with but it's not the last message :

SELECT * FROM chat 
WHERE idreceiver = 2 
GROUP BY idsender 
ORDER BY sentTime DESC

Add `LIMIT 1` after `ORDER BY`. – Eric Sep 03 '19 at 18:37 — Eric, Sep 03 '19 at 18:37

forpas · Accepted Answer · 2019-09-03T18:47:05.350

0

With NOT EXISTS:

SELECT c.* FROM chat c 
WHERE c.idreceiver = 2
AND NOT EXISTS (
  SELECT 1 FROM chat
  WHERE idreceiver = c.idreceiver AND idsender = c.idsender AND sentTime > c.sentTime
)

Or group by idsender and join to the table:

SELECT c.* 
FROM chat c INNER JOIN (
  SELECT idsender, MAX(sentTime) sentTime 
  FROM chat
  WHERE idreceiver = 2
  GROUP BY idsender 
) t ON t.idsender = c.idsender AND t.sentTime = c.sentTime 
WHERE c.idreceiver = 2

edited Sep 03 '19 at 18:47

answered Sep 03 '19 at 18:37

forpas

160,666
10
38
76

thank you @forpas, for your answer , but i did not meant the latest message he has received , i meant the latest message he has received from every user he has talked with , excuse me for the missunderstanding . – CoderTn Sep 03 '19 at 18:45
This is what the code does. Did you try it? – forpas Sep 03 '19 at 18:46
1

your answer is verified and working , thank you @forpas :) – CoderTn Sep 03 '19 at 18:57

score 0 · Answer 2 · answered Sep 03 '19 at 18:39

0

Try using this query:

SELECT
    *

FROM
    chat

WHERE
    idreceiver = 2
    AND
    sentTime = (SELECT MAX(sentTime) FROM chat WHERE idreceiver = 2)

answered Sep 03 '19 at 18:39

artemis

6,857
11
46
99

thank you @Jerry M, for your answer , but i did not meant the latest message he has received , i meant the latest message he has received from every user he has talked with , excuse me for the missunderstanding . – CoderTn Sep 03 '19 at 18:45

sql query to get the latest message sent

2 Answers2