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
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).