mysql - SQL: group by from other table and invert result -

i have problem sql query have table message , table recipient

message is

id    author    date -------------------- 0        1      2013-07-08 05:38:47 1        1      2013-07-13 05:38:47 2        1      2013-07-15 05:38:47 3        1      2013-07-15 05:38:47 4        2      2013-07-17 05:38:47 5        1      2013-07-28 05:38:47 

recipient is

id    m_id    recipient -------------------- 0        0      2 1        1      2 2        2      3 3        3      2 4        4      1 5        5      2 

i need return rows table message group recipient column table recipient last date in message table i'll try this

select m.*  message m inner join recipient r on (m.id = r.m_id) m.author = 1 group r.recipient order m.id desc 

return is

id    author    date -------------------- 2        1      2013-07-15 05:38:47 0        1      2013-07-08 05:38:47 

but need

id    author    date -------------------- 5        1      2013-07-28 05:38:47 2        1      2013-07-15 05:38:47 

please help

i use mysql server 5.1

---

i found solution problem

select m.*  ( select * recipient      1=1     order recipient.id desc ) r inner join message m on (r.m_id = m.id) m.author = 1 group r.recipient 

just reverse table recipient

very simple , fast in postgresql distinct on - not standard sql not available in every rdbms.

the question doesn't mention it, deriving code examples looking row "last date" each recipient for given author.

select distinct on (r.recipient)  m.*   message   m join   recipient r on r.m_id = m.id  m.author = 1 order  r.recipient, m.date desc, r.m_id -- break ties 

details multiple sql standard alternatives here:
select first row in each group group?

another solution basic, standard sql. works every major rdbms, including mysql (since tag has been added):

select m.*    message   m join   recipient r on r.m_id = m.id  m.author = 1 , not exists (    select 1      message   m1    join   recipient r1 on r1.m_id = m1.id     r1.recipient = r.recipient     ,    m1.author = 1    ,    m1.date > m.date    ) 

only row latest date passes not exists anti-semi-join.