Mysql select only unique rows (that appears only one time)

Question

Consider following data in table ->

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

...and skip the duplicate values.

@barudo I don't want to use WHERE email = 'example12@hotmail.com'. — Johny Pie, Mar 02 '17 at 23:44
@barudo needs to select multiple unique rows that does appears only one time, like First time Callers — Johny Pie, Mar 02 '17 at 23:45
so add it in your question. Something like: I need to pick up only records that exists only once... add also what fields in your table. — barudo, Mar 02 '17 at 23:59

score 2 · Accepted Answer · edited Mar 03 '17 at 00:49

2

The query you want is

SELECT id, email, whatEverColumn 
FROM table 
WHERE email IN (SELECT email 
               FROM table 
               GROUP BY email 
               HAVING COUNT(id) = 1)

edited Mar 03 '17 at 00:49

Qirel

25,449
7
45
62

answered Mar 02 '17 at 23:44

Kyoya

343
2
5

Hey, I'm not using primary id, I get this error -> #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table_name.date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Johny Pie Mar 02 '17 at 23:49
This error message sounds like one from SQL-Server, not from MySQL. But I'll update my post. – Kyoya Mar 02 '17 at 23:55
I'm sorry I was wrong. You may want to [set the `sql_mode`](http://stackoverflow.com/q/23921117/7605692) – Kyoya Mar 02 '17 at 23:59
1

@Kyoya In MySQL 5.7 that's the new default. Best to get used to it than to fight it and change server settings. – tadman Mar 03 '17 at 00:09
1

@tadman I have never used `GROUP BY` on MySQL 5.7. I see I have to expand my knowledge about MySQL. – Kyoya Mar 03 '17 at 00:22
This error happens only with phpmyadmin, I could use the syntax directly on php. I think phpmyadmin sets sql_mode by itself. Thanks @Kyoya for your time. – Johny Pie Mar 03 '17 at 00:27
1

Now it should have the correct syntax without disabling the SQL mode `ONLY_FULL_GROUP_BY`. – Kyoya Mar 03 '17 at 00:32

score 2 · Answer 2 · answered Mar 02 '17 at 23:47

2

Group by email and add having count(email) = 1

answered Mar 02 '17 at 23:47

vadim

178
1
9

Mysql select only unique rows (that appears only one time)

2 Answers2