Thanks for the advice, guys. After fiddling around with it a bit, I went for the age-old solution of changing the data and the requirements so it wasn't necessary :)
Thanks, Colin On 10/08/05, Kurt Welgehausen <[EMAIL PROTECTED]> wrote: > > 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 >
