CODEWITHSUNDEEP
mysql
Franco
Franco

Reputation: 2926

GROUP BY not returning correct results

This query

SELECT releases.id,releases.label_no_country,releases.date 
FROM releases 
INNER JOIN labels_user_has_recd luhr 
ON luhr.label=releases.label_no_country 
WHERE luhr.user='Si Quick' 
GROUP BY releases.id 
HAVING datediff(now(),releases.date) < 90 
ORDER BY releases.date DESC 
LIMIT 0,10

gives

id  label_no_country    date
605639  Church  2016-04-28
595303  Uncanny Valley  2016-04-21
605806  Dekmantel   2016-04-21
607276  Uncanny Valley  2016-04-21
604280  Delsin  2016-04-21
598653  Another Picture     2016-04-21
605016  Skylevel    2016-04-20
606342  Retreat     2016-04-14
599363  Frank Music     2016-04-12
599320  Spazio Disponibile  2016-04-08

I would like to not return any duplicates in column label_no_count (i.e. not Uncanny Valley twice.

I have tried using GROUP BY in the following query

SELECT releases.id,releases.label_no_country,releases.date 
FROM releases 
INNER JOIN labels_user_has_recd luhr 
ON luhr.label=releases.label_no_country 
WHERE luhr.user='Si Quick' 
GROUP BY releases.label_no_country 
HAVING datediff(now(),releases.date) < 90 
ORDER BY releases.date DESC 
LIMIT 0,10

but this only returns one result, rather than removing duplicates

id  label_no_country    date
599320  Spazio Disponibile  2016-04-08

Upvotes: 0

Views: 24

Answers (1)

Rahul
Rahul

Reputation: 77876

Move the HAVING to WHERE condition like

WHERE datediff(now(),releases.date) < 90

Your query should be

SELECT releases.id,releases.label_no_country,releases.date 
FROM releases 
INNER JOIN labels_user_has_recd luhr 
ON luhr.label=releases.label_no_country 
WHERE luhr.user='Si Quick'
AND datediff(now(),releases.date) < 90  
GROUP BY releases.label_no_country 
ORDER BY releases.date DESC 
LIMIT 0,10

Upvotes: 1

Related Questions