Problem With DISTINCT!

Question

Here is my query:

SELECT 
DISTINCT `c`.`user_id`,
`c`.`created_at`,
`c`.`body`,
(SELECT COUNT(*) FROM profiles_comments c2 WHERE c2.user_id = c.user_id AND c2.profile_id = 1) AS `comments_count`,
`u`.`username`,
`u`.`avatar_path` 
FROM `profiles_comments` AS `c` INNER JOIN `users` AS `u` ON u.id = c.user_id 
WHERE (c.profile_id = 1) ORDER BY `u`.`id` DESC;

It works. The problem though is with the DISTINCT word. As I understand it, it should select only one row per c.user_id.

But what I get is even 4-5 rows with the same c.user_id column. Where is the problem?

Answer 1

actually, DISTINCT does not limit itself to 1 column, basically when you say:

SELECT DISTINCT a, b

What you're saying is, "give me the distinct value of a and b combined" .. just like a multi-column UNIQUE index

Answer 2

distinct will ensure that ALL values in your select clause are unique, not just user_id. If you want to limit the results to individual user_ids, you should group by user_id.

Perhaps what you want is:

SELECT 
`c`.`user_id`,
`u`.`username`,
`u`.`avatar_path`,
(SELECT COUNT(*) FROM profiles_comments c2 WHERE c2.user_id = c.user_id AND c2.profile_id = 1) AS `comments_count` 
FROM `profiles_comments` AS `c` INNER JOIN `users` AS `u` ON u.id = c.user_id 
WHERE (c.profile_id = 1) 
GROUP BY `c`.`user_id`,
`u`.`username`,
`u`.`avatar_path`
ORDER BY `u`.`id` DESC;

Answer 3

DISTINCT works at a row level, not just a column level

If you want the DISTiNCT of only one column then you will have to aggregate the rest of the columns returned (MIN, MAX, SUM, AVG, etc)

SELECT DISTINCT (Name), Min (ID)
From MyTable

Answer 4

Distinct will try to return only unique rows, it will not return only 1 row per user id in your example.

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

Answer 5

You misunderstand. The DISTINCT modifier applies to the entire row — it states that no two identical ROWS will be returned in the result set.

Looking at your SQL, what value of the several available do you expect to see returned in the created_at column (for instance)? It would be impossible to predict the results of the query as written.

Also, you're using profile_comments twice in your SELECT. It appears that you're trying to obtain a count of how many times each user has commented. If so, what you want to do is use an AGGREGATE query, grouped on user_id and including only those columns that uniquely identify a user along with a COUNT of the comments:

SELECT user_id, COUNT(*) FROM profile_comments WHERE profile_id = 1 GROUP BY user_id

You can add the join to users to get the user name if you want but, logically, your result set cannot include other columns from profile_comments and still produce only a single row per user_id unless those columns are also aggregated in some way:

SELECT user_id, MIN(created_at) AS Earliest, MAX(created_at) AS Latest, COUNT(*) FROM profile_comments WHERE profile_id = 1 GROUP BY user_id