CODEWITHSUNDEEP
sqlsql-server
vvic
vvic

Reputation: 327

sql duplicates showing all data

Given this data 

id  Name        group           

1   Jhon        001

2   Paul        002

3   Mary        001

How can I get the duplicates values showing all the fields? The duplicate is only on group, id and name won't be duplicates.

Should end up looking like one of those (any would be valid):

:::::::::::::::::::::::::::::::::::::::::::::::
group  count values

001     2     1,3

:::::::::::::::::::::::::::::::::::::::::::::::
id  name        group
1   Jhon        001
3   Mary        001

I tried with

SELECT
    group, COUNT(*)
FROM
    people
GROUP BY
    group
HAVING 
    COUNT(*) > 1

But if I try to add id and name to the group by, it won´t find any duplicate.

Thanks in advance.

Upvotes: 1

Views: 68

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Just use exists:

select p.*
from people p
where exists (select 1
              from people p2
              where p2.group = p.group and
                    p2.id <> p.id
             );

This should be the most performant solution. With an index on people(group, id), it should have very good performance.

Note: All the advice to avoid using group as a column name is good advice. You should change the name.

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Try this.

SELECT Id, Name, [Group] 
FROM people
WHERE [Group] IN(
    SELECT [Group]
    FROM people
    GROUP BY [Group]
    HAVING COUNT(*) > 1)

Upvotes: 1

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Avoid using Group because it is a reserved keyword in SQL : 

 SELECT *
    FROM MyTable
    WHERE groups IN(
        SELECT groups
        FROM MyTable
        GROUP BY groups
        HAVING COUNT(*) > 1)

Check Execution here

Upvotes: 1

Tim Wooldridge
Tim Wooldridge

Reputation: 197

I would do an inner query to find the groups with more than one member, and then use that inner query to bring back a list of the names.

For example:

SELECT Id, Name, group
FROM people
WHERE group in
   (SELECT group
    FROM people
    GROUP BY group
    HAVING count(*) > 1);

Upvotes: 1

Related Questions