SQL- how to extract forum topics that are not repeats?

Question

On the home page of my website I want to display the latest posts to the forum however I don't want to show the same topic twice. How can I modify the code below to do this?

http://punbb.informer.com/wiki/punbb13/integration#recent_10_posts

Basically show the latest posts, but only once for each forum topic/thread.

Answer 1

change this line

'SELECT'    => 'p.id, p.message, t.subject',

to

'SELECT DISTINCT'   => 'p.id, p.message, t.subject',

Answer 2

Add a condition to keep only records where the post is the last post in the topic:

WHERE p.id = (
  SELECT pp.id 
  FROM posts AS pp
  WHERE pp.topic_id = t.id ORDER BY pp.posted DESC LIMIT 1
)

Answer 3

If you want only one value per topic, you could group by topic, and from each topic select the most recent post. Then, you could choose the top 10 topics.

I'll write it in SQL, and you can translate that to PHP:

SELECT p.id, p.message, o.subject
FROM
((SELECT t.id
  FROM posts AS p LEFT JOIN topics AS t ON p.topic_id = t.id
  GROUP BY t.id
  HAVING p.posted = MAX(p.posted) ) ids LEFT JOIN topics AS t ON ids.id = t.id) o
                                        LEFT JOIN posts AS p ON o.id = posts.topic_id
ORDER BY p.posted DESC
LIMIT '0,10'