1

Suppose for simplicity that I have a table t with columns id and value. I can find the ids that have more than one distinct value using a query such as

SELECT id, COUNT(DISTINCT(value)) AS count
FROM t
GROUP BY id
HAVING count > 1

Is there a more efficient way to do this? Can it be changed so that the count for each id stops as soon as more than one distinct value is found for it?

  • Is the question only for COUNT > 1 or a general one (so the 1 can be replaced by some other number)? – ypercubeᵀᴹ Dec 4 '14 at 1:47
  • Just COUNT > 1. – Patrick Dec 4 '14 at 1:48
2

You can test this variation. In theory, it would use an (id, value) index to find the min and max and would not have to count the distinct values at all:

SELECT id
FROM t
GROUP BY id
HAVING MIN(value) < MAX(value) ;
|improve this answer|||||
  • I'm sorry, I should have specified that the value may not be a number. Would this still work? – Patrick Dec 4 '14 at 1:53
  • If they are date/datetime/timestamps or chars, it would work fine. What is the type of value? – ypercubeᵀᴹ Dec 4 '14 at 1:53
  • It can be a varchar. – Patrick Dec 4 '14 at 1:57
  • Then, it will work. – ypercubeᵀᴹ Dec 4 '14 at 2:01
  • Is this equivalent to SELECT id, MIN(value) AS a, MAX(value) as b FROM t GROUP BY id HAVING a < b? – Patrick Dec 4 '14 at 2:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.