tleif tleif - 6 months ago 10
SQL Question

MYSQL Getting all values for a GROUP BY

DB has 3 columns (thing1, thing2, datetime). What I want to do is pull all the records for thing1 that has more than 1 unique thing2 entry for it.

SELECT thing1,thing2 FROM db WHERE datetime >= DATE_SUB(NOW(), INTERVAL 1 HOUR) GROUP BY thing1 HAVING COUNT(DISTINCT(thing2)) > 1;


Gets me almost what I need but of course the "GROUP BY" makes it so it only returns 1 entry for the thing1 column, but I need all the thing1,thing2 entries.

Any suggestions would be greatly appreciated.

Answer

I think you should use group by this way

SELECT thing1,thing2 
FROM db WHERE datetime >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
GROUP BY thing1, thing2  HAVING COUNT(*) > 1;