Find groups with exactly one value: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)
6
votes
1
answer
315
views
Given this data:
gid | val
1 | a
1 | a
1 | a
2 | b
3 | x
3 | y
3 | z
the following queries return groups (gid) that contain exactly one distinct value (val):
SELECT gid FROM t GROUP BY gid HAVING MIN(val) = MAX(val)
SELECT gid FROM t GROUP BY gid HAVING COUNT(DISTINCT val) = 1
People seem to suggest that the first variant would be faster (if assuming appropriate indexes exist then looking up MIN and MAX would be faster than counting all values). Is that a fact or a myth.
Asked by Salman Arshad
(461 rep)
Dec 14, 2018, 12:13 PM
Last activity: Jun 12, 2023, 11:38 AM
Last activity: Jun 12, 2023, 11:38 AM