CODEWITHSUNDEEP
mysql
tleif
tleif

Reputation: 95

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.

Upvotes: 1

Views: 36

Answers (3)

Uueerdo
Uueerdo

Reputation: 15941

Shamelessly copying Matt S' original answer as a starting point to provide an alternative...

SELECT db.thing1, db.thing2 
FROM db 
INNER JOIN (
    SELECT thing1, MIN(`datetime`) As `datetime` 
    FROM db 
    WHERE `datetime` >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
    GROUP BY thing1 
    HAVING COUNT(DISTINCT thing2) > 1
) AS subQ ON db.thing1 = subQ.thing1 AND db.`datetime` >= subQ.`datetime`
;

MySQL is very finicky, performance-wise, when it comes to subqueries in WHERE clauses; this JOIN alternative may perform faster than such a query.

It may also perform faster, than in it's current form, with the MIN removed from the subquery (and the join condition), and a redundant datetime condition on the outer WHERE supplied instead.

Which is best will depend on data, hardware, configuration, etc...

Sidenote: I would caution against using keywords such as datetime as field (or table) names; they tend to bite their user when least expected, and at very least should always be escaped with ` as in the example.

Upvotes: 1

Matt S
Matt S

Reputation: 15374

If I'm understanding what you're looking for, you'll want to use your current query as a sub-query:

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

The subquery is already getting the thing1s you want, so this lets you get the original rows back from the table, limited to just those thing1s.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

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;

Upvotes: 2

Related Questions