postgres group by aggregate function

Question

I have a messages table which 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 single most recent message for each sender.

This seems like a GROUP BY sender_id and ORDER BY created_at but I'm having trouble getting the most recent message selected.

I'm using postgres so need an aggregate function on the created_at field in the SELECT statement if I want to order by that field so I was looking at doing 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 'message' as well I have no idea what aggregate function to use on it. I basically just want the message that corresponds to the MAX created_at.

Is there some way of getting at this or am I approaching it the wrong way?

What version are you on?

Martin Smith
– Martin Smith

2010-09-12 19:36:35 +00:00
Commented Sep 12, 2010 at 19:36 — Martin Smith
– Martin Smith, Commented Sep 12, 2010 at 19:36

Quassnoi · Accepted Answer · 2010-09-14 14:44:16Z

9

This:

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 this:

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) for this to work fast.

You may find this article interesting:

PostgreSQL: Selecting records holding group-wise maximum

edited Sep 14, 2010 at 14:44

answered Sep 12, 2010 at 19:45

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Frank Heikens Over a year ago

This one gives a different result, the sort order is different.

Kevin Over a year ago

@Frank: you can use this a as sub query and reorder the results with your upper query.

Quassnoi Over a year ago

@Frank: the original question did not mention the order on sender_id, but here is the query with the corrected order.

johnnymire Over a year ago

In relation to the article, I can't use any of the 8.4 methods like windows functions as I'm on 8.3. The 2nd query you gave there gives an error (syntax error at or near "mi"). I need the order to be by date so can't use the DISTINCT ON idea there either.

Quassnoi Over a year ago

@johnnymire: see the post update. As for the second query, which line does it give the error on?

|

Kevin · Accepted Answer · 2010-09-12 21:10:37Z

1

Use a correlated sub query:

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

The sub query is reevaluated for each row processed by the upper query.

answered Sep 12, 2010 at 21:10

Kevin

3,4715 gold badges33 silver badges41 bronze badges

Comments

Gavin · Accepted Answer · 2010-09-14 15:02:54Z

1

Use distinct on:

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

answered Sep 14, 2010 at 15:02

Gavin

6,5603 gold badges28 silver badges25 bronze badges

Collectives™ on Stack Overflow

postgres group by aggregate function

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related