CODEWITHSUNDEEP
mysql
Chris Muench
Chris Muench

Reputation: 18318

mysql DISTINCT keyword not showing distinct rows

QUERY:

mysql> SELECT DISTINCT (stories.id), stories.news_type, story_keywords.keyword 
FROM (`stories`) LEFT JOIN `story_keywords` ON `stories`.`id`
=`story_keywords`.`story_id` 
WHERE `image_full_url` != '' AND `order` != 0 
AND `news_type` IN ('movie_review', 'movie') 
AND `keyword` IN ('topnews', 'toptablet') 
ORDER BY `created` DESC, `order` DESC LIMIT 5;

Result: (Notice that ID is NOT distinct)

+--------+--------------+-----------+
| id     | news_type    | keyword   |
+--------+--------------+-----------+
| 100895 | movie_review | topnews   |
| 100895 | movie_review | toptablet |
| 100798 | movie_review | toptablet |
| 100060 | movie_review | toptablet |
| 100060 | movie_review | topnews   |
+--------+--------------+-----------+
5 rows in set (0.00 sec)

Upvotes: 0

Views: 179

Answers (3)

Devart
Devart

Reputation: 121902

The DISTINCT option removes duplicated records, not field values. If you want to select distinct id, then you can use this query -

SELECT
  stories.id,
  GROUP_CONCAT(stories.news_type),
  GROUP_CONCAT(story_keywords.keyword)
FROM (`stories`)
LEFT JOIN `story_keywords`
ON `stories`.`id` = `story_keywords`.`story_id`
WHERE
  `image_full_url` != ''
  AND `order` != 0
  AND `news_type` IN ('movie_review', 'movie')
  AND `keyword` IN ('topnews', 'toptablet')
GROUP BY
  stories.id
ORDER BY
  `created` DESC
, `order` DESC
LIMIT
  5;

Upvotes: 0

Taryn
Taryn

Reputation: 247650

DISTINCT is applied to the entire row, since the keyword columns has multiple values for the different rows they match the criteria for DISTINCT. If you want to return one row, then you need to decide the which value of keyword to return.

If you want to concat the values together, then you can use GROUP_CONCAT():

SELECT stories.id, stories.news_type, GROUP_CONCAT(story_keywords.keyword)
FROM (`stories`) 
LEFT JOIN `story_keywords` 
  ON `stories`.`id`=`story_keywords`.`story_id` 
WHERE `image_full_url` != '' AND `order` != 0 
  AND `news_type` IN ('movie_review', 'movie') 
  AND `keyword` IN ('topnews', 'toptablet') 
GROUP BY stories.id, stories.news_type
ORDER BY `created` DESC, `order` DESC LIMIT 5;

The data would return like this:

| id     | news_type    |   keyword           |
+--------+--------------+---------------------+
| 100895 | movie_review | topnews,toptablet   |
| 100798 | movie_review | toptablet           |
| 100060 | movie_review | toptablet, topnews  |

Upvotes: 0

Marc B
Marc B

Reputation: 360572

select distinct applies to the row being fetched, not individual fields in that row. Each of the rows in your result set IS distinct.

Upvotes: 4

Related Questions