Using SQL to get the Last Reply on a Post

Question

I am trying to replicate a forum function by getting the last reply of a post.

For clarity, see PHPBB: there are four columns, and the last column is what I like to replicate.

I have my tables created as such:

• discussion_id (primary key)
• user_id
• parent_id
• comment
• status
• pubdate

I was thinking of creating a Link Table that would update for each time the post is replied to.

The link table would be as follow:

• discussion_id (primary key)
• last_user_id
• last_user_update

However, I am hoping that theres a advance query to achieve this method. That is, grabbing each Parent Discussion, and finding the last reply in each of those Parent Discussions.

Am I right that there is such a query?

---

Here is a update. I am still having a little trouble but I feel like I am almost there.

My current query:

SELECT 
`discussion_id`, 
`parent_id`,
`user_id` as `last_user_id`, 
`user_name` as `last_user_name` 
FROM `table1`, `table2`
WHERE `table1`.`id` = `table2`.`user_id`

Results:

discussion_id---------parent_id-----last_user_id-------last_user_name
30---------------------NULL-------------3--------------raiku
31---------------------30---------------2--------------antu
32---------------------30---------------1--------------admin
33---------------------NULL-------------3--------------raiku

Adding this:

GROUP BY `parent_id`

Turns it into:

discussion_id---------parent_id-----last_user_id-------last_user_name
32---------------------30---------------1--------------admin
33---------------------NULL-------------3--------------raiku

But I want it to turn it into:

discussion_id---------parent_id-----last_user_id-------last_user_name
30---------------------NULL-------------3--------------raiku
32---------------------30---------------1--------------admin
33---------------------NULL-------------3--------------raiku

Id 30, and ID 33 share the same parent_id: NULL but they are the "starting thread" or the "parent post"

They should not be combined, how would I go on by "Grouping" but "ignoring" null values?

Answer 1

You want GROUP BY. This should work out OK:

SELECT MAX(`pubdate`), `discussion_id`, `user_id` FROM `table` GROUP BY `parent_id`

You'll obviously need to fill in an appropriate the WHERE clause and LIMIT as needed.

Answer 2

This query will take the highest (thus assuming latest) discussion per parent_id. Not the neatest solution however ...

select discussion_id, user_id, pubdate
from tablename
where discussion_id in
(
  select max(discussion_id)
  from tablename
  group by parent_id
)

Answer 3

You could try something like this:

SELECT parent.discussion_id, 
child.discussion_id as last_discussion_id,
child.user_id as last_user_id,
child.pubdate as last_user_update
FROM Discussion parent
INNER JOIN Discussion child ON ( child.parent_id = parent.discussion_id )
LEFT OUTER JOIN Discussion c ON ( c.parent_id = parent.discussion_id AND c.discussion_id > child.discussion_id)
WHERE c.discussion_id IS NULL

The left join to Discussion c will not match when you have the post with the highest id, which should be the row that you want.