Franco Franco - 7 months ago 10
SQL Question

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

Answer

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
Comments