Get the row with max(timestamp)

Question

I need to select most recently commented articles, with the last comment for each article, i.e. other columns of the row which contains max(c.created):

SELECT a.id, a.title, a.text, max(c.created) AS cid, c.text?
FROM subscriptions s
JOIN articles a ON a.id=s.article_id
JOIN comments c ON c.article_id=a.id
WHERE s.user_id=%d
GROUP BY a.id, a.title, a.text
ORDER BY max(c.created) DESC LIMIT 10;

Postgres tells me that I have to put c.text into GROUP BY. Obviously, I don't want to do this. min/max doesn't fit too. I don't have idea, how to select this.

Please advice.

score 2 · Answer 1 · edited May 23 '17 at 11:48

In PostgreSQL, DISTINCT ON is probably the optimal solution for this kind of query:

SELECT DISTINCT ON (a.id)
       a.id, a.title, a.text, c.created, c.text
FROM   subscriptions s
JOIN   articles      a ON a.id = s.article_id
JOIN   comments      c ON c.article_id = a.id
WHERE  s.user_id = %d
ORDER  BY a.id, c.created DESC

This retrieve articles with the latest comment and associated additional columns.
Explanation, links and a benchmark in this closely related answer.

To get the latest 10, wrap this in a subquery:

SELECT *
FROM  (
    SELECT DISTINCT ON (a.id)
             a.id, a.title, a.text, c.created, c.text
    FROM   subscriptions s
    JOIN   articles      a ON a.id = s.article_id
    JOIN   comments      c ON c.article_id = a.id
    WHERE  s.user_id = 12
    ORDER  BY a.id, c.created DESC
    ) x
ORDER  BY created DESC
LIMIT  10;

Alternatively, you could use window functions in combination with standard DISTINCT:

SELECT DISTINCT
       a.id, a.title, a.text, c.created, c.text
      ,first_value(c.created) OVER w AS c_created
      ,first_value(c.text)    OVER w AS c_text
FROM   subscriptions s
JOIN   articles      a ON a.id = s.article_id
JOIN   comments      c ON c.article_id = a.id
WHERE  s.user_id = 12
WINDOW w AS (PARTITION BY c.article_id ORDER BY c.created DESC)
ORDER  BY c_created DESC
LIMIT  10;

This works, because DISTINCT (unlike aggregate functions) is applied after window functions.
You'd have to test which is faster. I'd guess the last one is slower.

Get the row with max(timestamp)

1 Answers1