Rails - Jul 2005 - [OT] tricky SQL query

I have a threaded message system, so my messages have a message ID, a
conversation ID and a date. To show the front page, I''d like to show
the conversations from newest to oldest, in order of the most recently
received message. So I can get the list of conversations using:

select conversation_id, max(msg_date) as how_recent
from messages
group by conversation_id
order by how_recent desc;

My question is: is it also possible to return the id of the most
recent message in this query? It seems to be quite a hard problem, and
also pretty database specific. Any tips?

Thanks,
Colin

Colin Fleming <colin.mailinglist@...> writes:
> 
> I have a threaded message system, so my messages have a message ID, a
> conversation ID and a date. To show the front page, I''d like to
show
> the conversations from newest to oldest, in order of the most recently
> received message. So I can get the list of conversations using:
> 
> select conversation_id, max(msg_date) as how_recent
> from messages
> group by conversation_id
> order by how_recent desc;
> 
> My question is: is it also possible to return the id of the most
> recent message in this query? It seems to be quite a hard problem, and
> also pretty database specific. Any tips?
> 
> Thanks,
> Colin
> 
select * from messages a
where a.msg_date = (
  select max(b.msg_date) 
   from messages b
   where a.conversation_id = b.conversation_id
)

assuming sub-selects work for your dbms