Retreiving duplicates from a database

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

SQL Server 2005 Forums

Transact-SQL (2005)

Retreiving duplicates from a database

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-04-12 : 16:34:54

How would I get three fields out of the database if they are duplicates?

I got it to work with one field but can't get it to work with the other two fields (BIC and PaymentAmt)

SELECT Claim,
COUNT(Claim) AS NumOccurrences
FROM Info
GROUP BY Claim
HAVING ( COUNT(claim) > 1 )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-12 : 16:41:46

SELECT Claim, OtherColumn1, OtherColumn2, COUNT(*)
FROM Info
GROUP BY Claim, OtherColumn1, OtherColumn2
HAVING COUNT(*) > 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2008-04-12 : 16:50:44

Thanks! Now if I want to add a where clause I just add it under the having count line?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-12 : 16:53:00

It depends.
HAVING is applied last. So if you want your resultset back faster, put it under WHERE.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-13 : 02:48:26

quote:
Originally posted by JJ297

Thanks! Now if I want to add a where clause I just add it under the having count line?

Nope where clause is added before having

SELECT Claim, OtherColumn1, OtherColumn2, COUNT(*)
FROM Info
WHERE conditions
GROUP BY Claim, OtherColumn1, OtherColumn2
HAVING COUNT(*) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources