user3597009 user3597009 - 1 year ago 69
MySQL Question

mysql DISTINCT() does not work as expected

I want to get list of posts sorted by number of comments, I've successfully ran following query but it gives repetitive values i.e posts repeat, I want unique of them sorted by number of comments, when I put DISTINCT() around my whole query, an error appears:
#1064 - 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 'UNIQUE(post.pname, post.pid FROM post, COMMENT WHERE post.pid = comment.pid ORD' at line 1

Query without DISTINCT() (Works but of course doesn't give unique values)

SELECT post.pname, post.pid
FROM post,
COMMENT WHERE post.pid = comment.pid
ORDER BY (

SELECT COUNT( * )
FROM COMMENT WHERE comment.pid = post.pid
GROUP BY post.pname
)


Query with DISTINCT() (doesn't work)

SELECT DISTINCT(post.pname, post.pid
FROM post,
COMMENT WHERE post.pid = comment.pid
ORDER BY (

SELECT COUNT( * )
FROM COMMENT WHERE comment.pid = post.pid
GROUP BY post.pname
))

Answer Source
SELECT distinct(post.pname) FROM post,COMMENT WHERE post.pid = comment.pid ORDER BY (SELECT COUNT( * ) FROM COMMENT WHERE comment.pid = post.pid GROUP BY post.pname) DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download