Carson Myers Carson Myers - 2 months ago 6
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.id, 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 = chats.id;


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

Answer

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.id, 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 = chats.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.

Comments