Re: [sqlite] Finding max of each group?

[Colin Fleming]

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 ()
> > 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 ()
> group by cid) t2
>   where t1.conversation_id = t2.cid and t1.updated_on = t2.mup
> 
> 
> Regards
>

[sqlite] Finding max of each group?

[Colin Fleming]

I'm writing a messaging system, and I have the following problem. I
have a conversations table, and a messages table that has a foreign
key to conversations. I can do something like the following to provide
a summary:

select conversation_id, count(*), max(unread), max(updated_on)
from messages
where conversation_id in ()
group by conversation_id;

(code may contain typos, sorry, haven't got my code right here).

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?

Thanks for any help,
Colin