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
>

Re: [sqlite] Finding max of each group?

[Kurt Welgehausen]

> 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

Re: [sqlite] Finding max of each group?

[Austin Ziegler]

On 8/10/05, Colin Fleming <[EMAIL PROTECTED]> wrote:
> 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?

Maybe, but I'm wondering why you're creating Yet Another Messaging
System instead of using something like XMPP (an IETF standard with
several open source implementations for both clients and servers).

That said, you can *probably* get it with a secondary query using
the information from the first.

  SELECT message_id
FROM messages
   WHERE conversation_id = ? -- use the conversation_id
 AND updated_on = ? -- use the updated on value

A little more dynamically, maybe:

  SELECT message_id
FROM messages
   WHERE conversation_id, updated_on IN
  (SELECT conversation_id, MAX(updatd_on)
 FROM messages
WHERE conversation_id IN ()
GROUP BY conversation_id);

One might even be able to combine some of this as a subquery on the
FROM line, but that would require a bit of experimentation on your
part, since you're going to be more familiar with your data.
Something like (completely untested here):

  SELECT m.conversation_id, q.message_id, COUNT(m.*), MAX(m.unread),
 MAX(m.updated_on)
FROM messages m, (SELECT mm.conversation_id, mm.message_id
FROM messages mm
   WHERE mm.conversation_id, mm.updated_on IN
 (SELECT mmm.conversation_id,
 MAX(mmm.updated_on)
FROM messages mmm
   WHERE mmm.conversation_id IN ()
   GROUP BY conversation_id)) q
   WHERE m.conversation_id = mm.conversation_id
   GROUP BY m.conversation_id, q.message_id;

Maybe.

-austin
-- 
Austin Ziegler * [EMAIL PROTECTED]
   * Alternate: [EMAIL PROTECTED]

[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