Finding 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

Finding duplicates

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2011-07-14 : 09:50:53

I've wrote this query to find duplicate websites with in my database.

SELECT website,
COUNT(website) AS NumOccurrences
FROM wce_Contact
GROUP BY website
HAVING ( COUNT(website) > 1 )

What I want to know is how do I write this query to bring in other fields, but I still want to just find duplicates based on website.

I though this query would work, but produces an error

SELECT website, expressemail
COUNT(website) AS NumOccurrences
FROM wce_Contact
GROUP BY website
HAVING ( COUNT(website) > 1 )

Error: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'website'.

Any help is appreciated!

JT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-14 : 09:54:03

Let's say one website is in your table 10 times with 10 different expressemail values. Do you want to see all 10 rows or just one row? If just one then which of the 10 expressemail values do you want?

Be One with the Optimizer
TG

Topaz
Posting Yak Master

199 Posts

Posted - 2011-07-14 : 10:00:10

Sorry I didn't make that very clear. I would like to be able to view all 10 records that have a different expressemail.

JT

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-14 : 10:31:39

Here's one way:


select c.*
from   (
       SELECT website
       FROM wce_Contact
       GROUP BY website
       HAVING ( COUNT(website) > 1 )
       ) d
join   wce_contact c
       on c.website = d.website
order by c.website

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources