• 0

[SQL] Select column with x occurences in another column?


Question

I know the answer will probably be really simple but I just can't see it :pinch:. I would like to select only the tuples from column X that have two or more occurrences of different values in column Y.

As a sample table:

X | Y

id1 M1

id2 M2

id2 M2

id1 M2

So the desired result would be:

X

id1

Since id1 is the only entry in X that has two different values in Y.

Something like:

SELECT DISTINCT x FROM table WHERE [??] > 1;

But I can't think of the expression that would work. :wacko:

Edited by Minchino
Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

SELECT DISTINCT X
FROM Table1
WHERE X in (SELECT X FROM Table1 GROUP BY X HAVING COUNT(X) > 1)
GROUP BY X,Y
HAVING COUNT(*) = 1

Seemed to work for me. First makes sure that there is more than one entry in the X column for a certain ID, then checks that there at least 2 seperate entries taking into consideration that there is more than one occurance in X, so if you get a count = 1 then you know that the 2 occurances have different y's.

Link to comment
Share on other sites

  • 0

Thanks, need to have a look at Having clauses again I think, using:

SELECT X, FROM Table GROUP BY X HAVING Count(DISTINCT Y) > 1;

Seems to do the trick for me. :)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.