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

duplicates

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-30 : 11:39:22

Okay I'm back again with a different problem and table(s)

How do I list the duplicates on the screen Tried this but it's giving me the NumOccurrences as 2 I want to see them as well.

SELECT ProjNum,Rec1,Rec2,
COUNT(ProjNum) AS NumOccurrences
FROM ProjectTable
GROUP BY ProjNum, Rec1, Rec2
HAVING ( COUNT(ProjNum) > 1 )

My results:


ProjNum  Rec1    Rec2    numOccurrences
72188  	  C4	 PC2	     2
46125  	  C2	 PC1	     2
60350  	  E 	 PC6	     2
63605  	  C5	 PC2	     2
3591  	  E4	 PC6	     2

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-30 : 11:50:55

The requirement is not clear atleast for me.
You want to see the duplicate records ?

Can you post your expected output .

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 12:07:51

...see them as well

What is Them?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-30 : 12:18:11

Maybe:


SELECT *
FROM ProjectTable P
	JOIN
	(
		SELECT ProjNum,Rec1,Rec2, 
			COUNT(ProjNum) AS NumOccurrences
		FROM ProjectTable
		GROUP BY ProjNum, Rec1, Rec2
		HAVING COUNT(ProjNum) > 1
	) D
		ON P.ProjNum = D.ProjNum
			AND P.Rec1 = D.Rec1
			AND P.Rec2 = D.Rec2

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-30 : 13:16:09

Yes I wanted to see the duplicate recoreds. Thanks Ifo your query did it.

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-30 : 13:30:52

Yes I wanted to see the details of the rows I consider duplicates

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-06-30 : 13:53:06

Here's a duplicate:


ProjNum  Rec1   Rec2
82093  	  C4	PC2	
82093  	  C4	PC2

I wanted to see these and how to get rid of them into another table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources