Geek Answers Handbook

Postgres group by aggregate function

I have a message table that looks like this:

+------------+-------------+----------+ | sender_id | created_at | message | +------------+-------------+----------+ | 1 | 2010-06-14 | the msg | | 1 | 2010-06-15 | the msg | | 2 | 2010-06-16 | the msg | | 3 | 2010-06-14 | the msg | +------------+-------------+----------|

I want to select the only last message for each sender.

This is similar to GROUP BY sender_id and ORDER BY created_at, but I am having trouble displaying the most recent message.

I use postgres, so I need the aggregate function in the created_at file in the SELECT statement if I want to order this field, so I was looking to do something like this as an initial test

 SELECT messages.sender_id, MAX(messages.created_at) as the_date FROM messages GROUP BY sender_id ORDER BY the_date DESC LIMIT 10;

This seems to work, but when I want to select the “message”, I don’t know what function of the unit to use on it. I just want the message to match MAX created_at.

Is there a way to get to this or am I approaching it wrong?

+4
source share
3 answers

It:

 SELECT * FROM ( SELECT DISTINCT ON (sender_id) * FROM messages ORDER BY sender_id, created_at DESC ) q ORDER BY created_at DESC LIMIT 5

or that:

 SELECT (mi).* FROM ( SELECT ( SELECT mi FROM messages mi WHERE mi.sender_id = m.sender_id ORDER BY created_at DESC LIMIT 1 ) AS mi FROM messages m GROUP BY sender_id ) q ORDER BY (mi).created_at DESC LIMIT 5

Create an index on (sender_id, created_at) so that it works quickly.

You may find this article interesting:

+6
source

Use correlated subquery:

 select * from messages m1 where m1.created_at = ( select max(m2.create_at) from messages m2 where m1.sender_id = m2.sender_id );

The subprocess is reevaluated for each row processed by the top query.

+1
source

Use various:

  SELECT DISTINCT ON (sender_id) sender_id,created_at,message FROM messages ORDER BY sender_id,created_at DESC
0
source

More articles:


All Articles