> select conversation_id, count(*), max(unread), max(updated_on)
> from messages
> where conversation_id in (<list of ids>)
> group by conversation_id;
>
> I use max(updated_on) to get the date of the most recent message in
> the conversation. Is there a way to return the ID of this message?
Assuming that the messages table has a column called msg_id,
select t1.conversation_id, t2.c, t2.mun, t2.mup, t1.msg_id
from messages t1,
(select conversation_id cid, count(*) c,
max(unread) mun, max(updated_on) mup
from messages
where cid in (<list of ids>)
group by cid) t2
where t1.conversation_id = t2.cid and t1.updated_on = t2.mup
Regards
