CODEWITHSUNDEEP
mysqlsql
anon_swe
anon_swe

Reputation: 9355

SQL: Filter out rows where column value occurs more than once

I have a MySQL table that looks like this:

id    |    label
----------------
1          "john"
1          "henry"
1          "sara"
2          "henry"
3          "tim"

So a given id can have multiple labels. I want to keep only the rows where the id has a single label. So the correct output for the above table would be:

id    |    label
----------------
2          "henry"
3          "tim"

I was thinking I should group by id and find the count of labels for each id. Then I'd take only rows with a count of 1. 

WITH temp as
(SELECT id
FROM original_table
GROUP BY id
HAVING COUNT(id) > 5)

SELECT *
FROM original_table ot
WHERE ot.id in temp.id

Does that look close?

Thanks!

Upvotes: 0

Views: 2555

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I think aggregation is the simplest method:

select id, min(label) as label
from original_table t
group by id
having count(*) = 1;

Upvotes: 1

Avi
Avi

Reputation: 1845

Yes your approach is correct and you may have to change your having count condition and while referring to CTE you may have to change your syntax little , but you can do it without CTE as well in the same line with exists condition. 

Create table  temp  (ID int , Label varchar(10)); 

insert into  temp values 
(1  ,      "john" ), 
(1   ,     "henry" ) , 
(1   ,     "sara" )  , 
( 2   ,    "henry"  ) , 
(3   ,     "tim" ) ; 


select t.ID , t.Label from temp t 
where exists (
select ID, count(1) Dups  from temp t1 where t1.ID = t.ID group by ID having count(1) 
= 1)

Output: 

    ID, Label
    2, henry
    3, tim

Upvotes: 0

sticky bit
sticky bit

Reputation: 37507

Assuming that the pair id and label is unique, you can use NOT EXISTS and a correlated subquery.

SELECT t1.id,
       t1.label
       FROM original_table t1
       WHERE NOT EXISTS (SELECT *
                                FROM original_table t2
                                WHERE t2.id = t1.id
                                      AND t2.label <> t1.label);

Upvotes: 0

Tony
Tony

Reputation: 644

You could try this: 

SELECT t.id, t.label
FROM tbl AS t
JOIN (SELECT id FROM tbl GROUP BY id HAVING count(label) = 1) AS t1
ON t.id = t1.id;

Upvotes: 0

Martin
Martin

Reputation: 16453

You could just use a join to only include ID's that occur once in a sub-query:

SELECT  id,
        label
  FROM  original_table ot
    INNER JOIN  (
                SELECT  id
                  FROM  original_table
                  GROUP BY id
                  HAVING COUNT(*) = 1
                ) a ON a.id = ot.id;

Or you could use an IN clause:

SELECT  id,
        label
  FROM  original_table
  WHERE id IN (SELECT   id
                  FROM  original_table
                  GROUP BY id
                  HAVING COUNT(*) = 1
              );

Upvotes: 1

Related Questions