only do not select duplicated values mysql?

Question

I am looking for a way to run a simple SELECT statment. I have a table which has two columns: id and email.

I want to run a SELECT statment that won't return duplicate values. For example, take the following data:

1   example@hotmail.com  
2   example12@hotmail.com  
3   example@hotmail.com 
4   example@hotmail.com

I want it to return only the following:

2   example12@hotmail.com

Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) — Sanders the Softwarer, Dec 05 '17 at 11:06

score 2 · Answer 1 · answered Dec 05 '17 at 10:46

2

Use aggregation count(*) and check the result of aggregate function using having clause to filter out those records which are not duplicated

select *
from demo
group by email
having count(*) = 1

answered Dec 05 '17 at 10:46

M Khalid Junaid

it give me like this #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'users.prov.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Mømĕn ẶłmĦsĕĕrỷ Dec 05 '17 at 11:15
@MømĕnẶłmĦsĕĕrỷ change * to email only – M Khalid Junaid Dec 05 '17 at 11:15

score 0 · Answer 2 · answered Dec 05 '17 at 10:48

0

select id,email from table group by email having count(*) =1;

answered Dec 05 '17 at 10:48

Sherry

it give me like this #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'users.prov.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Mømĕn ẶłmĦsĕĕrỷ Dec 05 '17 at 11:14
@MømĕnẶłmĦsĕĕrỷ which MySQL version are you using? – Sherry Dec 05 '17 at 12:10

2 Answers2