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 (<list of ids> )
> 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 (<list-of-ids>)
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 (<listofids>)
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]
