mysql – SQL query to fetch the latest records

Question:

There is a messages table that looks like this: _id (prim key), author, client, time, content.

Let it have the following meanings:

 _id | author | client | time  | content
--------------------------------------
   1 |  user  |  you   | 21:52 | hi   
   2 |  user  |  you   | 20:43 | wassup   
   3 |  user2 |  you   | 17:45 | привет   
   4 |  user2 |  you   | 18:20 | :(   

From this table you need to get ONE (last) message from each author'a, where client = you .

Those. using a request, you need to return messages with id 1 and 4 ( hi and :( ). How can this be done?

Answer:

sqlfiddle

SELECT t1.*
FROM test t1 INNER JOIN (SELECT author, client, max(time) as mtime
                         FROM test
                         WHERE client = 'you'
                         GROUP BY author, client) t2
     ON t1.author = t2.author AND t1.client = t2.client AND t1.time = t2.mtime