CODEWITHSUNDEEP
mysql
Rbijker.com
Rbijker.com

Reputation: 3114

MySql: select unique values

I have a table with a column named 'emails'. I need a query that selects the emails that only appears once in the table.

So when the column 'emails' has ([email protected], [email protected], [email protected]) I just want to select [email protected].

I have tried the following query:

SELECT DISTINCT `emails` FROM `table`

But the problem is that it selects [email protected] and [email protected].

I need a query what will only select [email protected].

Can anyone help me?

Upvotes: 0

Views: 55

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You can use the following 

select 
count(*) as cnt,
email from table 
group by email having cnt = 1

If you have an auto-incremented primary key then you have other approach as well something as

select * from table t1
where not exists (
 select 1 from table t2
 where t1.email = t2.email
 and t1.id <> t2.id
);

Upvotes: 1

Related Questions