Finding Duplicates

Jun 5, 2008

how can i find duplicates in my database? i want to find them in the website and expressemail field

View 5 Replies


Finding Duplicates In SQL

Jul 26, 2002

I have a customer database with the following structure:

Dept ID (int)
Section (varchar)

I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.

Example Data:

Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance

The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.

I am not in control of the data design and can't create a bitwise field to achieve this.


View 1 Replies View Related

Finding Duplicates

Jul 7, 2004

I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)

thank you

SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
HAVING Count([TM#])>1;

View 14 Replies View Related

Finding Duplicates - Is This Right?

May 16, 2007

I've done a search and I THINK I've got my head round this, but I'd be very grateful if someone could reassure me:

SELECT Email FROM List2 WHERE List2.Email= List1.Email
) AND List1.Email <> '44'

That will give me every email address from list one that (a) appears in list two, and (b) isn't '44'. Right?

And to find all the emails from List1 that DON'T occur in List2 (and aren't '44'), I just put "NOT" in front of "EXISTS". Right?

Sorry for asking an obvious question but I'm having a real mental block here. :o

View 6 Replies View Related

Finding Duplicates Using More That One Key

Jun 16, 2008

Hi guys,

I was just wondering if any one knows how to find duplicate keys using more than one field. I used the below key to find those people who exists in list1 but don't exists in list2. I realized that the results had some duplicates which was expected but how do I then find all those duplicate people. I know how to do it if there was a primary key present I would have done a count (distinct cardnumber) > 1 and i would have done the select statement like this distinct cardnumber, but how do I do it with more that one key??

View 1 Replies View Related

Finding Duplicates

Jul 20, 2005

I have a company table and I would like to write a query that will return tome any duplicate companies. However, it is a little more complicated thenjust matching on exact company names. I would like it to give me duplicateswhere x number of letters at the beginning of the company name match AND xnumber of letters of the address match AND x number of letters of the citymatch. I will be doing this in batches based on the first letter of thecompany name. So for example I will first process all companies that startwith the letter "A".So for all "A" companies I want to find companies where the first 5 lettersin the company name match and the first 5 characters of the address fieldmatch and the first 5 characters of the city match. THANKS!!!

View 3 Replies View Related

Finding Duplicates?

Oct 9, 2007

I'm trying to find duplicates. Any help will be greatly appreciated! Here's my query:

I'm trying to find only the records where the "Sales_Header_Your_Reference" field is used more then once with a different "Sales Header Document ID" So it looks like this
Sales Header Document ID Sales_Header_Your_Reference
1955718 0002377729
2082721 0002478945
2082728 0002478976
2093598 0002487318
2093599 0002487318
2093601 0002487332
I hope im clear enough, here is my query. I get then listed right, but the count is all wrong, I only want to show the ones , like the one i have highlighted.

Code Block

Select COUNT(Sales_Header_Your_Reference),Tbl_Sales_Header.Sales_Header_Document_Id,Tbl_Sales_Header.Sales_Header_Your_Reference
FROM dbo.Tbl_Sales_Header
INNER JOIN dbo.Tbl_Sales_Invoice_Header
ON dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = dbo.Tbl_Sales_Invoice_Header.Sales_Invoice_Header_Bill_Customer_No AND
dbo.Tbl_Sales_Header.Sales_Header_Order_DateTime = dbo.Tbl_Sales_Invoice_Header.Sales_Invoice_Header_Order_Datetime
WHERE (dbo.Tbl_Sales_Header.Sales_Header_Order_DateTime > CONVERT(DATETIME, '2007-09-15 00:00:00', 102)) AND
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'Butler') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'MWI') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'NLS') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'HSI') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'NLSMVD') OR
(dbo.Tbl_Sales_Header.Customer_Bill_Customer_No = 'RNEXT')
GROUP by Tbl_Sales_Header.Sales_Header_Document_Id,Tbl_Sales_Header.Sales_Header_Your_Reference
HAVING COUNT(Sales_Header_Your_Reference) > 1
order by Tbl_Sales_Header.Sales_Header_Document_Id, Tbl_Sales_Header.Sales_Header_Your_Reference

View 9 Replies View Related

Finding Duplicates Regardless Of Special Characters - Help Plz

Nov 22, 2005

I am trying to move distinct data from one table to another based on two columns regardless of special characters such as : ( ) ; ' " / - _ = >< etc. The two columns that are the defining factors that I need to match as duplicates are Col1 & Col3, the rest I want to get the maximum data from each column for that row (I hope this made sense).

Here is what I have tried, but it does not seem to work:


max(Col2)as Col2,
max(Col4)as Col4,
max(Col5)as Col5,
max(Col6)as Col6,
max(Col7)as Col7,
max(Col8)as Col8,
max(Col9)as Col9
where patindex('%[^-:]%',Col1) = 0 AND patindex('%[^-:]%',Col3) = 0
into Newtable
from dbo.CLEANING_bk
group by Col1,Col3

Basically if there are 10 rows of duplicate data based on Col1 & Col3 (regardless of special characters), I want to move the distinct info to a new table with the maximum amount of info from the other columns of the duplicate rows.

If row 3, 4,5, & 6 are all considered duplicate and Row3/Col2 has info in it that row 4 & 5/Col2 does not, I want to combine it with the single row I export to the new table retaining as much information from the rows of the duplicates as possible.

Can anyone help and let me know why my script is not wanting to play nice?

View 14 Replies View Related

Finding Duplicates - What On Earth Am I Overlooking?

Aug 15, 2006

I have two lists of contacts. They're similar. I want a list of all the contacts whose email address occurs only in the first list.

returns 13460

returns 13220

returns 0 rows

How can it be returning no rows? What am I failing to take into consideration?


View 6 Replies View Related

Finding And Replacing Duplicates In Sql Using A Script

Mar 27, 2008

Finding and replacing duplicates

I have this project; I am trying to work on but can€™t because of duplicated data in that table.

I tried to make the culume not accept duplicates, but I need to clean it up first and I can just delete them.

Currently the values are 11 digit numbers and I need to replace them with 00000000001

incrementing, making my last one 00000003000

Any idea on where to even begin? I am new to this.

What I am trying to do.

Find replicates numbers in TRAN_DATA

Replace each with a new number but increment the new number starting with 00000000001 and ending with 00000003000

With this I knew I had 3000 plus.

View 5 Replies View Related

Transact SQL :: Comparing Records - Finding Matches / Duplicates

Nov 20, 2015

I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.

At first, I wanted to get a count of how many there are.

I need to compare records where these fields are matched:

MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile).
MATCHED: (email, firstname, mobile)
But not MATCH: (lastname, phone)
MATCHED: (email, firstname, lastname)
But not MATCH: (phone, mobile)

View 9 Replies View Related

SQL Server 2012 :: Finding Duplicates And Show Original / Duplicate Record

Nov 3, 2014

There are many duplicate records on my data table because users constantly register under two accounts. I have a query that identify the records that have a duplicate, but it only shows one of the two records, and I need to show the two records so that I can reconcile the differences.The query is taken from a post on stack overflow. It gives me 196, but I need to see the 392 records.

How to identify the duplicates and show the tow records without having to hard code any values, so I can use the query in a report, and anytime there are new duplicates, the report shows them.



View 5 Replies View Related

T-SQL (SS2K8) :: Rank Duplicates But Only Rows Involved In Duplicates?

Oct 22, 2014

I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.

As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)

Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.

DROP TABLE [dbo].[TestBusiness];
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,


View 9 Replies View Related

Converting Duplicates Records Into Non Duplicates?

Jan 26, 2015

Is there a query or a way to convert duplicates value in a column to non duplicates.

View 14 Replies View Related

SQL Duplicates

Mar 2, 2006

I need some help.
I have created a database that looks like the following:
FirstName Table link to Main Table.
I have created a Stored procedure that looks like this:
Create procedure dbo.StoredProcedure
   @FirstName   varchar(20)
Declare FirstNameID int
Insert Into Main Table
Select @FirstNameID = Scope_Identity()
How could I redesign this to check if a value exists and if it exists then simply use that value instead of creating a new duplicate value?

View 2 Replies View Related

Getting Rid Of Duplicates

Jun 27, 2001

I have a dilema..... I have a databas eof about 60,000 users and i need to get rid of those users where there is a duplicate email address. I have written an asp utilty that works but is far too taxing on our little server and i thinkk itwill kill it. what it does is for each email address it compares it against all the others.... so for each address it checks against 60,000 other records 60,000 times.... you know what i mean. its pretty phucked.... i tested it on just one record and took about 5mins.

anyway ive been trying to do it in SQL with no luck

here is a simplified version of my dilema

tombomb tom@mail23
thomas tom@mail13

so what i want to do is cut it down so there are no duplicate email addresses. I want the table looking like this:


Can you think of a way to do this?

i tried the following but it gets rid of duplicates of other fields which is not what i want to do

CREATE table distinct_records
SELECT name, Email, Age
FROM duplicate_records
GROUP BY name, Email, Age;

any ideas guys?


View 1 Replies View Related


Nov 7, 2005

i've imported a number of spreadsheets into a database
the schema is

directoryid firstname, lastname, extension
703168 Andrew Lim 78094
703154 Joseph Egan 78888
704548 Andrew Lim 78094

I realized the spreadsheet had a large number of duplicates. How can i delete the duplicates ?
like deleting directoryid 703168 only.

View 1 Replies View Related


Nov 9, 2005

i'm trying to get duplicates out of the my database

FROM iac.dbo.sf_profil
GROUP BY FirstName, surname, internalextension
HAVING COUNT(*) > 1 order by firstname, surname

How do i alter the query just retrieve records which have firstname and lastname which are similar but different extension numbers ?

View 2 Replies View Related


May 8, 2008

This is the query which shows me the duplicates
Some of the records have more than one records
I would like to know how to delete the extra records so that I will end up with one record per row.

Count(*) as 'count'
group by
having count(*) > 1
order by
count desc

View 4 Replies View Related


Jun 13, 2008


Is there a way to find duplicates in one field? For example my query has person_nbr and for each person_nbr on one day they could have used multiple payer_names. I want to be able to count each person_nbr one time but also I want to group by description(which is the name of the provider) and by payer name to see how many person's that the provider seen with each payer. My problem is that if the person had more than one payer they are counted twice. Is there some type of aggregate function to use the first payer in the list??

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
join person_payer d on tmp.person_id = d.person_id
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp

Thanks in Advance!

View 4 Replies View Related


May 10, 2006

I'll see if I can explain this clearly. The query below
selects rows from the "hdr_ctl_nbr_status" table if the value in the field "tcn" from that table is found in the table "temp_tcn". I want all fields from the "hdr_ctl_nbr_status" table to be selected BUT only one row. In other words for a tcn with a value "12345678" there are 10 rows returned from the hdr_ctl_nbr_status table, I want only 1. Is there a way I can use SELECT DISTINCT to do this ? I know this usually functions on one or more fields but I want the DISTINCT to be on tcn only BUT return all fields in the query.

Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK)
where h.tcn in (select tcn from temp_tcn)

Thanks, sorry if this is too confusing. Jeff

View 14 Replies View Related

No Duplicates Please

Mar 10, 2008

Hi, All,

I have two columns of int data in the a table, as my example data shows below.

I want my data returned to be something like those in #test3, but my question is this, how can I do it without using #test2 and #test3?

By the way, the business requirement doesn't care it's min/max or any ID when one side has duplicated values.


Use tempdb

if object_ID ('#test') is not null drop table #test

create table #test (col1 int, col2 int)
insert into #test
Select 123, 222 union
Select 124, 222 union
Select 125, 222 union
Select 111, 223 union
Select 111, 224

if object_ID ('#test2') is not null drop table #test2
create table #test2 (col1 int, col2 int)
Insert into #test2
Select distinct col1, min(col2) from #test group by col1

if object_ID ('#test3') is not null drop table #test3
create table #test3 (col1 int, col2 int)
Insert into #test3
Select min(col1), col2 from #test2 group by col2

Select * from #test3

View 2 Replies View Related

Msg 512 But No Duplicates!?!

Jul 28, 2005

I am attempting to execute the Stored Procedure at the foot of thismessage. The Stored Procedure runs correctly about 1550 times, butreceive the following error three times:Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,Line 68Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I've done some digging, and the error message is moderatelyself-explanatory.The problem is that there is no Line 68 in the Stored Procedure. It'sthe comment line:-- Need to find out how many hours the employee is scheduled etc.Also, there are no duplicate records in the Employee table nor theWeeklyProfile table. At least I assume so - if the following SQL todetect duplicates is correct!SELECT E.*FROMEmployee Ejoin(select EmployeeIDfromEmployeeGroup by EmployeeIDhaving count(*) > 1) as E2On(E.EmployeeID = E2.EmployeeID)SELECTW.*FROMWeekProfile Wjoin(SelectWeekProfileIDFROMWeekProfileGROUP BYEmployeeID, MondayHours, WeekProfileIDHAVING COUNT(*) > 1) AS W2ONW.WeekProfileID = W2.WeekProfileIDNOTE: In the second statement, I have tried for MondayHours thruFridayHours.Anyone got any ideas? The TableDefs are set up in this thread:<>The Stored Procedure that causes the error is here:--************************************************** ***********CREATE PROCEDURE BackFillNetworkHoursASDECLARE @EmployeeID intDECLARE @TimesheetDate DateTimeDECLARE @NumMinutes intDECLARE @NetworkCode int-- Get the WorkID corresponding to Project Code 2002SELECT@NetworkCode = WorkIDFROM[Work]WHERE(WorkCode = '2002')-- Open a cursor on a SELECT for all Network Support Employees whereany single workday comprises fewer than 7.5 hoursDECLARE TooFewHours CURSOR FORSELECTEmployeeID,CONVERT(CHAR(8), Start, 112) AS TimesheetDate,SUM(NumMins) AS TotalMinsFROM(SELECTTI.EmployeeID,W.WorkCode,TI.Start AS Start,SUM(TI.DurationMins) AS NumMinsFROMTimesheetItem TI LEFT JOIN[Work] W ON TI.WorkID = W.WorkIDWHERE EXISTS(SELECT*FROMEmployee EWHERE((TI.EmployeeID = E.EmployeeID) AND(E.DepartmentID = 2)))GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS xGROUP BYEmployeeID,CONVERT(char(8), Start, 112)HAVINGSUM(NumMins) < 450ORDER BYEmployeeID,CONVERT(CHAR(8), Start, 112)-- Get the EmployeeID, Date and Number of Minutes from the cursorOPEN TooFewHoursFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesWHILE (@@FETCH_STATUS=0)BEGINDECLARE @NewWorkTime datetimeDECLARE @TimesheetString varchar(50)DECLARE @Duration intDECLARE @RequiredDuration int-- Set the correct date to 08:30 - by default the cast from thecursor's select statement is middaySET @TimesheetString = @TimesheetDate + ' 08:30'SET @NewWorkTime = CAST(@TimesheetString AS Datetime)-- Need to find out how many hours the employee is scheduled to workthat day.SET @RequiredDuration = CASE (DATEPART(dw, @NewWorkTime))WHEN 1 THEN(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 2 THEN(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 3 THEN(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 4 THEN(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 5 THEN(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 6 THEN(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 7 THEN(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))ENDIF @NumMinutes < @RequiredDurationBEGIN-- Set the Start for the dummy work block to 08:30 + the number ofminutes the employee has already worked that daySET @NewWorkTime = DateAdd(minute, @NumMinutes, @NewWorkTime)-- Set the duration for the dummy work block to be required durationless the amount they've already workedSET @Duration = @RequiredDuration - @NumMinutes-- Now we have the correct data - insert into table.INSERT INTO TimesheetItem(EmployeeID,Start,DurationMins,WorkID)VALUES(@EmployeeID,@NewWorkTime,@Duration,@NetworkCode)ENDFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesENDCLOSE TooFewHoursDEALLOCATE TooFewHoursGO--************************************************** ***********ThanksEdward

View 3 Replies View Related

Getting Rid Of Duplicates

Jul 20, 2005

I have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4,COL5, COL6).... I need to be able to select all columns/rows whereCOL3, COL4, and COL5 are unique....I have tried using DISTINCT and GROUP BY, but both will only allow meto access columns COL3, COL4, and COL5..... i need access to allcolumns...I just want to get rid of duplicate rows (duplicates ofCOL3, COL4, and COL5)...Thanks in advance.Joe

View 1 Replies View Related

Sum Of Duplicates

Apr 23, 2008

Anyone know how to sum duplicated values so it only sums the distinct values?

View 3 Replies View Related

Duplicates Only

May 21, 2008

I have the following data.

CustID RoleID RelatID StartDate EndDate

20 RIX AGQ 5/05/2007 31/12/9999

20 RIX AGR 10/06/2007 31/12/9999

18 LRS AGQ 3/09/2004 31/12/9999

22 SRT AGP 5/03/2007 31/12/9999

22 SRT AGP 10/03/2007 31/12/9999

I wish to SELECT only the records that have duplicates. So, in the above data example, RoleID of LRS should be excluded.

How to do?

View 11 Replies View Related


Feb 12, 2007

Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

View 16 Replies View Related

How Do I Stop These Duplicates

Feb 25, 2004

I have these two tables and I cant prevent duplicates.


And I get something like:ItemID Item CategoryID
1 item1 1
3 item3 1
1 item1 2 <---duplicate
1 item1 3 <---duplicate
2 item2 3
4 item4 3
Thanks in advance

View 4 Replies View Related

Dealing With Duplicates

Feb 15, 2002

Just looking for advise on dealing with duplicates in database.
I have a contact table that have a bunch of duplicated customer records.
My goal is to combine all duplicated records into one record.
This involves couple tables:contact,contact history ,calendar.
All tables related by common column "accountno".
What would be the best approach for this?

Thank you,

View 2 Replies View Related

Deleting Duplicates

Apr 17, 2001


does anyone out there know how to delete dulicate records in a table? I've looked in BOL and I can't seem to find it. Please advise.

thanks, mark

View 2 Replies View Related

Removing The Duplicates

Aug 2, 2000

I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.

I need a query to do this.

Can anybody help.


View 3 Replies View Related

Identifying Duplicates

Sep 5, 2000

I need to delete ONLY the duplicate rows from a table..Can anyone suggest me a way to do that thing..i used the query..

"Delete from <TableName> where SlNo IN ( Select SlNo from <TableName> group
by SlNO having Count(*) > 1)"

The resultant is all the original rows also getting deleted with the duplicates..anyone please help me out..

Thank you in advance

View 5 Replies View Related

Indexing And Duplicates

Aug 5, 2002


I’m using SQL Server 2000. I have a table called Contacts and I would like to be able to have the UserID as an indexed column and to ignore duplicates. I set up the following properties within my SQL Server database table:

Table Name: Contacts
Selected Index: IX_UserID
Column Name: UserID
Order: Ascending
Create Unique
Ignore Duplicate Key

Every time I try to enter duplicates for the UserID column; I get an error that says, “Cannot enter duplicate key row in object ‘Contacts’. Can anyone explain this? Is it possible to create an index column with duplicate data?


View 4 Replies View Related

Copyrights 2005-15, All rights reserved