I've a sample table"
A | 2
A | 0
A | 0
B | 1
B | 1
C | 2
C | 1
And I want Result to be:
A | 2
A | 0
A | 0
C | 2
C | 1
i.e, I don't want the row which has same value in column two
-
Your table structure is unclear.Paul Draper– Paul Draper2013-11-15 06:14:01 +00:00Commented Nov 15, 2013 at 6:14
-
1Your question is unclearLinga– Linga2013-11-15 06:16:45 +00:00Commented Nov 15, 2013 at 6:16
-
1Shouldn't other rows be removed, since they have the same values in column 2?Paul Draper– Paul Draper2013-11-15 06:17:09 +00:00Commented Nov 15, 2013 at 6:17
-
Why 2nd and 3rd lines in result didn't removed?Narek– Narek2013-11-15 06:18:57 +00:00Commented Nov 15, 2013 at 6:18
-
If the second column has more than one value for same value in column 1 then I want all to be selecteduser2992030– user29920302013-11-15 06:23:42 +00:00Commented Nov 15, 2013 at 6:23
|
Show 1 more comment
2 Answers
Assuming your table has columns 'ch' for the character and 'value' for the number you can do following:
select t.ch, t.value
from test t
join (
select ch, count(distinct value) as 'dst'
from test
group by ch
having count(distinct value)=1
) as q on t.ch!=q.ch;
the subsuquery finds the ch's which only have one distinct value. Those are not included in the join.
Comments
Not the most effective query but I guess you want something like this :
SELECT * FROM test WHERE C1 NOT IN (
SELECT C1 FROM test
GROUP BY C1 HAVING COUNT(DISTINCT(C2)) = 1
);
I assume that you don't want the records with only 1 column one example; well if you do :
(for example if there is a "D | 1" row and you want that)
SELECT * FROM test WHERE C1 NOT IN (
SELECT C1 FROM test
GROUP BY C1 HAVING COUNT(DISTINCT(C2)) = 1 AND COUNT(C2) > 1
);
Edit : Here is the sqlfiddle link
