Uttam Kumar Roy Uttam Kumar Roy - 4 years ago 97
SQL Question

Get last comment for each chat room through GROUP By not working in MySql

I have a simple chat system. In that chat system user can see comments that has sent to him. I want to show only the last (one) comment per chat room but it's not working with my Query. So please help me to gain a proper result

Table Definition:

CREATE TABLE users (
`user_id` int,
`user_name` varchar(55)
);

CREATE TABLE chat_room (
`chat_room_id` int,
`description` varchar(55)
);
CREATE TABLE chat_room_comments (
`comment_id` int,
`chat_room_id` int,
`sender_id` int,
`comment` varchar(55),
`send_to` int,
`read_status` tinyint
);


And Data:

INSERT INTO users
(`user_id`, `user_name`)
VALUES
(1, 'The Rock'),
(2, 'Dwayne'),
(3, 'Johnson')
;

INSERT INTO chat_room
(`chat_room_id`, `description`)
VALUES
(1, 'Room 1'),
(2, 'Room 2'),
(3, 'Room 3')
;

INSERT INTO chat_room_comments
(`comment_id`,`chat_room_id`, `sender_id`,`comment`, `send_to`,`read_status`)
VALUES
(1,1,2, 'Room 1 Hello The Rock',1,0),
(2,1,1, 'Hello Dwayne',2,0),
(3,1,3, 'Hello Dwayne',2,0),
(4,2,1, 'Hello Johnson',3,0),
(5,2,3, 'Hello Dwayne',2,0),
(6,2,2, 'Room 2 Hello The Rock',1,0),
(7,3,1, 'Hello Johnson',3,0),
(8,3,3, 'Room 3 Hello The Rock',1,0),
(9,3,3, 'Room 3 Hello The Rock',1,0),
(10,3,2, 'Hello Johnson',3,0)
;


My Query ( Not Working Properly as my requirement ):

SELECT * FROM chat_room CR
LEFT JOIN chat_room_comments CRC ON CRC.chat_room_id = CR.chat_room_id
LEFT JOIN users U ON U.user_id = CRC.sender_id
WHERE
CRC.send_to = 1
GROUP BY CRC.comment_id
ORDER BY CRC.comment_id DESC


Query Result:

enter image description here

Expected Result:

enter image description here

SqlFiddle

Answer Source

Firstly you need to get last comment_id per chat_room_id

SELECT chat_room_id, MAX(comment_id) as last_comment_id FROM chat_room_comments 
WHERE send_to = 1 GROUP BY chat_room_id

and then use this results to JOIN other data

SELECT CR.*, CRC.* , U.*
FROM  chat_room_comments CRC 
JOIN (
    SELECT chat_room_id, MAX(comment_id) as last_comment_id FROM chat_room_comments 
    WHERE send_to = 1 GROUP BY chat_room_id
) LCRC ON CRC.chat_room_id = LCRC.chat_room_id AND CRC.comment_id = LCRC.last_comment_id
JOIN chat_room CR ON CRC.chat_room_id = CR.chat_room_id 
LEFT JOIN users U ON U.user_id = CRC.sender_id
ORDER BY CRC.comment_id DESC 

or

SELECT CR.*, CRC.* , U.*
FROM  chat_room_comments CRC 
JOIN chat_room CR ON CRC.chat_room_id = CR.chat_room_id 
LEFT JOIN users U ON U.user_id = CRC.sender_id
WHERE CRC.comment_id IN (SELECT MAX(comment_id) as last_comment_id FROM chat_room_comments 
    WHERE send_to = 1 GROUP BY chat_room_id)
ORDER BY CRC.comment_id DESC 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download