Need help to find duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help to find duplicates

Author

Topic

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-06-05 : 16:15:57

Hello,

I have a little problem and I need your help. Below is a sample of the table.

ID | MEM_ID | GRPNUM | *
1 | 405 | 203 |
2 | 405 | 203 |
3 | 405 | 204 |
4 | 406 | 785 |
5 | 505 | 243 |
6 | 505 | 243 |

The top two rows and the bottom two rows are duplicates. I need for a query to list all records that have two or more duplicates, ignoring records that have no duplicates.

select Id, MEM_ID, GRPNUM , [and other fields]
from #tmpTable
order by Id, MEM_ID, GRPNUM

The above query returns all list, but I need it to be modified to return a list of all rows that has duplicates. Any help is appreciated.

The expected result I need is this.

ID | MEM_ID | GRPNUM | *
1 | 405 | 203 |
2 | 405 | 203 |
5 | 505 | 243 |
6 | 505 | 243 |

MS SQL 2008

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-05 : 17:16:21

[code];WITH cte AS
(
SELECT *,COUNT(*) OVER (PARTITION BY mem_id,grpnum) AS N
FROM Tbl
)
SELECT ID, MEM_ID, GRPNUM FROM cte WHERE N > 1;[/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-06-05 : 19:56:05

[CODE]select id, mem_id, grpnum
from Tbl t
where exists(select *
from Tbl t1
where t1.id <> t.id
and t1.mem_id = t.mem_id
and t1.grpnum = t.grpnum
)[/CODE]
=================================================
May my silences become more accurate. -Theodore Roethke (1908-1963)

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-06-06 : 08:21:38

Thanks. Both examples worked.

mathomas73
Starting Member

23 Posts

Posted - 2013-06-06 : 11:00:08

Hi, in this last example you've posted, can you tell me what the difference is between t1 and t, as you only have one table right?

Thanks

Rajan Sahai
Starting Member

8 Posts

Posted - 2013-06-06 : 11:36:34

Select * from tbl F1
where F1.grpnum in ( Select F2.grpnum from tbl F2
Group By F2.grpnum
Having COUNT(F2.grpnum) > 1
)

The above query is simple and easy to understand. Refer Interactive SQL from unspammed

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-06 : 12:26:04

quote:
Originally posted by Rajan Sahai

Select * from tbl F1
where F1.grpnum in ( Select F2.grpnum from tbl F2
Group By F2.grpnum
Having COUNT(F2.grpnum) > 1
)

The above query is simple and easy to understand. Refer Interactive SQL from www.fundasmadeeasy.com

Just be careful if you can have null values in your table - depending on the behavior you want, it may or may not work. In the example below, the rows with mem_id=406 are not picked up

CREATE TABLE #tmp(id INT, mem_Id INT, grpnum INT);

INSERT INTO #tmp VALUES (1,405,203),(2,405,203),(3,405,204);
INSERT INTO #tmp VALUES (4,406,NULL),(5,406,NULL);

Select * from #tmp F1
where F1.grpnum in ( Select F2.grpnum from #tmp F2
Group By F2.grpnum
Having COUNT(F2.grpnum) > 1
)

DROP TABLE #tmp;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources