mankee mankee - 27 days ago 8
MySQL Question

Select all records where ID appears once SQL

I have a table of the form:

User1 A
User2 A
User2 B
User3 C
User4 A
User4 C
User5 D


and out of this I need to pick ONLY the users who appear once. So for example, from the above I want User1, User3, User5. I could use something like
row_number()
to remove duplicates, however this would return one row for all users, even those who have more than 1 entry, which is not what I want. I can't use
DISTINCT
either as for example
User2 A
and
User2 B
would not be caught as they are not equal.

Answer

Use GROUP BY:

select username
from t
group by username
having count(*) = 1;

If you know there are no duplicates in the second column, then with the right indexes the following might be faster:

select t.*
from t
where not exists (select 1 from t t2 where t2.username = t.username and t2.col2 <> t.col2);
Comments