Get newest result with GROUP BY in SQL based on JOIN

Question

I have two SQL tables users and messages which look like this:

user_id | username
--------|---------
      1 | alice
      2 | bob
      3 | carol

message_id | sending_user_id | message | created_utc
-----------|-----------------|---------|-------------
          1|               1 | a       | 67
          2|               1 | b       | 68
          3|               3 | c       | 69
          4|               2 | d       | 70
          5|               3 | e       | 71
          6|               1 | f       | 72

I'm trying to write an SQL query that will result in the newest message for each user ordered by the created_utc of the message desending. So the result should be like this:

message_id | sending_user_id | message | created_utc | sending_username
-----------|-----------------|---------|-------------|----------------------
          6|               1 | f       | 72          | alice
          5|               3 | e       | 71          | carol
          4|               2 | d       | 70          | bob

I'm stuck on how to guarantee that the message is always the newest with the following query:

SELECT messages.message_id, messages.sending_user_id, messages.message, messages.created_utc, users.username AS sending_username
FROM messages 
LEFT JOIN ON users 
WHERE messages.sending_user_id=users.user_id 
GROUP BY messages.sending_user_id
ORDER BY messages.created_utc DESC

EDIT: I am using PostgreSQL 9.3.5

Lots of identical questions. http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564 — Erwin Brandstetter, Nov 07 '14 at 02:55

Gordon Linoff · Accepted Answer · 2014-11-07T01:36:18.953

With Postgres, I would recommend distinct on:

SELECT DISTINCT ON (m.sending_user_id) m.message_id, m.sending_user_id, m.message, m.created_utc,
       u.username AS sending_username
FROM messages m LEFT JOIN
     users u
     on m.sending_user_id = u.user_id 
ORDER BY m.sending_user_id, m.created_utc DESC;

If you want the final order by, use a subquery:

SELECT um.*
FROM (SELECT DISTINCT ON (m.sending_user_id) m.message_id, m.sending_user_id, m.message, m.created_utc,
              u.username AS sending_username
      FROM messages m LEFT JOIN
           users u
           on m.sending_user_id = u.user_id 
      ORDER BY m.sending_user_id, m.created_utc DESC
     ) um
ORDER BY created_utc;

This guarantees the newest message, but does not order the table according to the `created_utc` column. — mverderese, Nov 07 '14 at 01:33

score 0 · Answer 2 · answered Nov 07 '14 at 01:46

I think the best (and most flexible) ANSI SQL solution is the one with NOT EXISTS:

SELECT    m1.message_id, m1.sending_user_id, m1.message, m1.created_utc, users.username AS sending_username
FROM      messages m1 
LEFT JOIN users ON (m1.sending_user_id=users.user_id)
WHERE     NOT EXISTS (
            SELECT *
            FROM   messages m2
            WHERE  m2.sending_user_id = m1.sending_user_id
            AND    m2.created_utc < m1.created_utc
          )

Get newest result with GROUP BY in SQL based on JOIN

2 Answers2