Solved

Finding records that occur more than once in a recordset/table

Posted on 2007-11-19
3
245 Views
Last Modified: 2012-05-05
Need some help with a query.

I have a table called "contacts" which has many fields, in particular, I am interested in contact_email and contact_last_name

I want to create query that will return on records that have contact_email and contact_last_name appearing more than once in the table so I can identify duplicates.

For example,

contact_email          contact_last_name
--------------------------------------------------
john@doe.com        Doe
jane@doe.com        Doe
john@doe.com        Doe

would return

john@doe.com, Doe, 2 based on the logic above thus identifying that John Doe is like a duplicate record.

Please provide actual query.  Thanks.
0
Comment
Question by:ccleebelt
3 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 500 total points
ID: 20314287
select contact_email, contact_last_name
from tablename
group by contact_email, contact_last_name
HAVING COUNT(contact_email)>1 OR COUNT(contact_last_name)>1
0
 

Author Closing Comment

by:ccleebelt
ID: 31409990
Thanks
0
 
LVL 6

Expert Comment

by:wshark83
ID: 20314364
Same query but giving you the number of occurances for Emails and Lastname.

select contact_email, contact_last_name, count(contact_email) as EmailCount, COUNT(contact_last_name) as LastNameCount
from tablename
group by contact_email, contact_last_name
HAVING COUNT(contact_email)>1 OR COUNT(contact_last_name)>1
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with a query 6 74
CROSS APPLY 4 51
Unable to save view in SSMS 21 70
Why i am getting a star, SSMS does not show me any error. Division Error 5 23
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question