Mysql GROUP BY and unique values

Question

I have a table in MySQL:

SID DID MessageType DateOf  Message 
1   255 LOG     2010-01-17 15:02:05 Log msg 1    
2   255 USER    2010-01-17 19:02:05 User msg 1
3   211 LOG     2010-01-17 15:06:55 Log msg 2    
4   211 ADMIN   2010-01-17 22:06:55 Admin msg 1  

I need to get last (by DateOf) Message and MessageType, groupped by DID, ordered by DateOf DESC

Right result would be:

DID MessageType DateOf  Message 
211 ADMIN       2010-01-17 22:06:55 Admin msg 1
255 USER        2010-01-17 19:02:05 User msg 1

How can I do this select query? MySQL 5.1

Answer 1

SELECT * FROM `test` WHERE (DID,DateOf) IN (
    SELECT DID,MAX(DateOf) FROM `test` GROUP BY DID
) GROUP BY DID ORDER BY DateOf  DESC

Subquery selects max DateOf for each DID. Outer query selects rows with those DateOf and DID. GROUP BY in outer query ensures that there is only one row in result for each DID (in case there are more than one row with same DID and DateOf in the table, then it returns one of them without any particular order).

Answer 2

SELECT * FROM test t JOIN (
    SELECT DID, MAX(DateOf) AS DateOf FROM test GROUP BY DID
) t2 ON (t.DID = t2.DID AND t.DateOf = t2.DateOf)
ORDER BY t.DateOf DESC;

Another solution:

SELECT * FROM test t WHERE DateOf = (
    SELECT MAX(DateOf) FROM test t2 WHERE t2.DID = t.DID
) ORDER BY DateOf DESC;

When I was solving this some time ago I googled this useful article: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/