Carson Myers Carson Myers - 1 year ago 87
SQL Question

Select non-aggregate column in a group by, based on a related aggregate column

I'm trying to get a list of conversations out of a database, and I'd like the most recent message to be displayed along with them. I'm having trouble finding a query that lets me do it (and which isn't N+1).

I have these tables:

chats(id, user_id, post_id, created_at)
messages(id, user_id, chat_id, body, created_at)

I had a query like this in mind:

select, chats.user_id, m.latest from chats
inner join (
select chat_id, max(created_at) as latest from messages
group by chat_id
) as m on m.chat_id =;

But I want to add the message body from the row corresponding to the
result. Is it possible to get a related column like that?

Answer Source

With standard SQL this can be done using window functions (see sagi's answer).

With Postgres, the proprietary distinct on() is often faster than the equivalent solution using a window function:

select, chats.user_id, m.body, m.latest 
from chats
  join (
    select distinct on (chat_id) chat_id, body, created_at as latest 
    from messages
    order by chat_id, created_at desc;
  ) as m on m.chat_id =; 

The solution using a window function is more flexible though. You could also get the latest two (or three or ...) messages with that, whereas distinct on() will only get you one.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download