Get the row with the latest date in a group?

Question

I have two tables:

items:
| item_id | title |

comments:
| comment_id | item_id | posted_at | author_id | text |

Where posted_at is the time a comment was posted.

How can I get a list of all items, with the time each of them was last commented on and the author_id of that last comment?

Ok, so you want a table result like this: `item_id|posted_at| author_id`? — , Dec 29 '14 at 15:39

O. Jones · Answer 1 · 2014-12-29T16:58:30.720

You need the most recent comment for each item. There are three parts to that.

First: most recent

SELECT  MAX(comment_id) FROM comments GROUP BY item_id

Second: most recent comment

SELECT comments.author_id, comments.posted_at
  FROM comments
 WHERE comments.comment_id IN 
     (SELECT  MAX(comment_id) FROM comments GROUP BY item_id)

Third. Most recent comment for each item.

SELECT items.item_id, items.title, comments.author_id, comments.posted_at
  FROM items
  LEFT JOIN comments
          ON items.item_id = comments.item_id
         AND comments.comment_id IN 
                 (SELECT  MAX(comment_id) FROM comments GROUP BY item_id)

The trick here is to find the single most recent comment for each item, and then use it. The left join operation preserves those items that have no comments. This query uses comment_id as a proxy to search for the latest posted_at. It assumes comment_id is an autoincrement column, and that later comments have higher comment_id values than earlier comments.

A compound index on the comments table on (item_id, comment_id) will help performance here, by accelerating the GROUP BY subquery.

yes, you're right ... I delete my answer and upvote yours. Thank you — , Dec 29 '14 at 15:54
If items list is to be limited somehow to items with some specified properties, how can this request be optimized not to acquire comments data on unneeded items? Should it be optimized this way, will it give a better performance? — Mikhail Batcer, Dec 29 '14 at 16:13

AdamMc331 · Answer 2 · 2014-12-29T16:19:28.027

For this, you don't necessarily need the 'items' table if all you want are 'item_id'.

Start by writing a query that gets the latest comment time for each item_id like this:

SELECT item_id, MAX(posted_at) AS latestComment
FROM comments
GROUP BY item_id;

Now, you can join that with your comments table on the condition that the item_id and latestComment columns match to get the latest comment author for each item:

SELECT c.item_id, c.author_id, c.posted_at
FROM comments c
JOIN(
   SELECT item_id, MAX(posted_at) AS latestComment
   FROM comments
   GROUP BY item_id) temp ON temp.item_id = c.item_id AND temp.latestComment = c.posted_at;

If you do need any information form the items table, you can just join the above query to the items table using the item_id column to get what you need.

EDIT

If you want to add requirements for items you can join the above table, and put them in either the WHERE clause or even the ON statement of your join, like this:

SELECT c.item_id, c.author_id, c.posted_at
FROM comments c
JOIN items i ON i.item_id = c.item_id AND i.title LIKE '%Apple%'
JOIN(
   SELECT item_id, MAX(posted_at) AS latestComment
   FROM comments
   GROUP BY item_id) temp ON temp.item_id = c.item_id AND temp.latestComment = c.posted_at;

I just made up an example requirement. This query should pull the latest comment for all items that have a title containing the word 'Apple'. Note that this is an inner join, so you will only see items that do have comments. If you want to see all items, I recommend an outer join.

If items list is to be limited somehow to items with some specified properties, how can this request be optimized not to acquire comments data on unneeded items? Should it be optimized this way, will it give a better performance? — Mikhail Batcer, Dec 29 '14 at 16:14
@MikhailBatcer I am, admittedly, not an expert on performance. Please see my edit for how you can restrict items. My recommendation is to try the query first. If it does not preform well, we can take it from there. — AdamMc331, Dec 29 '14 at 16:20

score 0 · Answer 3 · answered Dec 29 '14 at 15:39

0

You can try using max(posted_at) group by item_id and join it with you comments table on these 2 columns.

answered Dec 29 '14 at 15:39

Xion

318
1
5
19

score 0 · Answer 4 · answered Dec 29 '14 at 15:39

What about;

    Select  title,author_id,MAX(posted_at) as LastTime From items i
    join comments c
    on c.item_id = i.item_id
    group by title,author_id

This should give you what you are looking for if the same post exists multiple times. If not, you could even remove the MAX and add other columns.

Get the row with the latest date in a group?

4 Answers4