CODEWITHSUNDEEP
mysqlsqldatetimetime-seriesaggregate-functions
Mikhail Batcer
Mikhail Batcer

Reputation: 2065

Get the row with the latest date in a group?

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?

Upvotes: 2

Views: 61

Answers (4)

O. Jones
O. Jones

Reputation: 108841

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.

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16730

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.

Upvotes: 1

Hozikimaru
Hozikimaru

Reputation: 1166

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.

Upvotes: 0

Xion
Xion

Reputation: 318

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

Upvotes: 0

Related Questions