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 john@email.com
2 John hihi 10:46 john@email.com
3 Peter hihi 10:47 peter@email.com
4 John hihi 10:48 john@email.com
5 John hihi 10:49 john@email.com
6 John hihi 10:50 john@email.com
7 Mary hihi 10:51 mary@email.com
8 John hihi 10:52 john@email.com
9 Peter hihi 10:53 peter@email.com
10 John hihi 10:54 john@email.com

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 john@email.com
9 Peter hihi 10:53 peter@email.com
7 Mary hihi 10:51 mary@email.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?

Answers


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

Need Your Help

conditional bind_result with prepared statement in php

php mysql prepared-statement

I am not able to bind result in php variable &price but with different conditional statements.It gives following error.

Wide exec for C/C++

c++ c exec wchar-t

Is there a wchar_t version of exec[lv][pe] (i.e. an exec that uses wchar_t as path and wchar_t as arguments)?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.