sql select duplicates and singles

Question

so i have a database table like:

NAME  name_ID
---------------
Joao    1
Maria   3
Joao    1 
carlos  2 
carlos  2

i want to do a select query that displays all duplicates only like this :

NAME  name_ID
---------------
Joao    1
Joao    1 
carlos  2 
carlos  2

and other select query that displays singles like so :

 NAME  name_ID
  ---------------
    Maria 3

I guess you didn't google "sql duplicates"? http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — Digital Chris, Dec 28 '15 at 14:45

Gordon Linoff · Answer 1 · 2015-12-28T14:59:12.967

This would be simpler if you had a unique id column in each table. I encourage you to design tables with primary keys.

In any case, you can do this with a query of the form for duplicates:

select t.*
from databasetable t join
     (select name, count(*) as cnt
      from databasetable
      group by name
     ) tt
     on t.name = tt.name
where cnt > 1;

For singletons, the comparison would be cnt = 1.

EDIT:

With a unique id and an index on (name, id), the following is probably faster for duplicates:

select t.*
from databasetabe t
where exists (select 1
              from databasetable t2
              where t2.name = t.name and t2.id <> t.id
             );

Singletons would use not exists instead.

score 0 · Answer 2 · answered Dec 28 '15 at 14:51

0

select name from table group by name haveing count(*)=1 ###for Maria

select * from table where not in (previous select) order by name

answered Dec 28 '15 at 14:51

Adrian Duta

81
4

sql select duplicates and singles

2 Answers2