Select Distinct from multiple tables with order by

I'm stucked with a Mysql query, can you help me?

I have two tables:

user

id | name

1 | foo1

2 | foo2

3 | foo3

posts

id | id_user | created_at | kind

1 | 2 | 15-03-2011 | a

1 | 2 | 14-03-2011 | b

2 | 3 | 13-03-2011 | a

1 | 2 | 12-03-2011 | b

What I want is to retrieve the latest post开发者_运维问答 of each user (the kind doesn't matter) ordered by de creation date.

How can I do that?

Thank you guys

One possible query is:

SELECT
u.id,
(SELECT MAX(p.created_at) FROM posts AS p WHERE u.id = p.id_user) AS latest  
FROM 
user AS u;

although the dependent subquery may not be the best solution to this. Example output:

users:

+------+------+
| id   | name |
+------+------+
|    0 | test |
|    1 | one  |
+------+------+

posts:

+------+---------+------------+------+
| id   | id_user | created_at | kind |
+------+---------+------------+------+
|    0 |       0 | 2011-02-05 | a    |
|    1 |       1 | 2011-02-06 | b    |
|    2 |       0 | 2011-02-03 | a    |
|    3 |       1 | 2011-02-02 | b    |
+------+---------+------------+------+

output:

+------+------------+
| id   | latest     |
+------+------------+
|    0 | 2011-02-05 |
|    1 | 2011-02-06 |
+------+------------+

You can also add an ORDER BY latest DESC to the end of the query if you wish to get an ordered list of the latest posts across all user IDs.

Using a GROUP BY on id_user and the max post date ?

Something like that :

SELECT u.name, p.id_user, MAX( p.created_at ) 
FROM posts AS p
LEFT JOIN user AS u ON u.id
WHERE p.id_user = u.id
GROUP BY id_user