How to write query to retreive last update rows from SQLite/ MySQL?
I have a table chat. The column are "id", "sender", "message", "time", "sender_email". Some chat history which inserted into chat like:
1 John hihi 10:45 email@example.com 2 John hihi 10:46 firstname.lastname@example.org 3 Peter hihi 10:47 email@example.com 4 John hihi 10:48 firstname.lastname@example.org 5 John hihi 10:49 email@example.com 6 John hihi 10:50 firstname.lastname@example.org 7 Mary hihi 10:51 email@example.com 8 John hihi 10:52 firstname.lastname@example.org 9 Peter hihi 10:53 email@example.com 10 John hihi 10:54 firstname.lastname@example.org
I would like to get the last message from people who appeared in the table. If there is one who appear in the table many time, just get his last message. So the result I would like to get should be:
10 John hihi 10:54 email@example.com 9 Peter hihi 10:53 firstname.lastname@example.org 7 Mary hihi 10:51 email@example.com
Is it possible to do so?? So far, I just write a sql:
SELECT DISTINCT name from chat ORDER BY id DESC
But seems it cannot display what I want. Any solution?
You have to determine which messages are the last one for each user (I'm doing this by time but you could do it by id too if you know a higher value definitely equals a later message), then get that message's data:
SELECT chat.* FROM chat INNER JOIN ( SELECT MAX(time) AS time, sender FROM chat GROUP BY sender ) lastMsg ON chat.time = lastMsg.time AND chat.sender = lastMsg.sender
This also assumes there are no ties for time. If this is possible you'll have to go by id instead:
SELECT chat.* FROM chat INNER JOIN ( SELECT MAX(id) AS id FROM chat GROUP BY sender ) lastMsg ON chat.id = lastMsg.id