如何在一个请求中为每种类型选择MAX

Question

I use mysql 5.1.

This is my DB schema :

id     user_id     type     created_at
1       32          X       2012-11-19
2       32          Y       2012-11-18
3       30          X       2012-11-16
4       32          Z       2012-11-17
5       31          Y       2012-11-13
6       32          Z       2012-11-9

I want that my SQL query returns the user_id, type, and the lastest created_at for each type. I tried to make this request: SELECT max(type) FROM notification WHERE user_id=34 ORDER BY type but it returns only the latest created_at.

Answer 1

You cannot do this in really simple query because GROUP BY (which you are missing by the way in your query) doesn't guarantee that it won't return values mixed from multiple rows.

SELECT * FROM notifications AS t1
INNER JOIN (
    SELECT type, MAX(`created_at`) AS max_created_at
    FROM notification
    WHERE user_id=34
    GROUP BY `type`
) AS t2
    ON t1.type = t2.type
        AND t1.created_at = t2.max_created_at