Using 'distinct' in a MySQL query

Question

I have the following query.

 "SELECT p.author_name, p.author_id, DISTINCT p.topic_id, t.title
               FROM `ibf_posts` p, `ibf_topics` t WHERE p.topic_id = t.tid  ORDER BY pid DESC LIMIT 8"

When I run it, I get the following mysql Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT p.topic_id, t.title FROM `ibf_posts` p, `ibf_topics` t WHERE p' at line 1

if I remove the DISTINCT keyword, then the query works without a problem.

What am I doing wrong?

This scheme is from Invision Power Board post and topic table. I am trying to get the title of the last 8 topics which has the newest posts. In the list of top latest posts, I don't want the same topic to appear more than once. I want a unique list of titles.

table: ibf_posts -pid -author_name -author_id -topic_id

table: ibf_topics -tid -title

tid is same as topic_id

Answer 1

Distinct needs to be before p.author_name. You can't choose only p.topic_id to be distinct in this case. If what you're wanting to do is what I think it is, you should look up the GROUP BY clause.

Answer 2

It's

SELECT DISTINCT ...

You can't specify DISTINCT only for a single column; it only works for keeping complete duplicate records out of the result set.