Hello Everyone, I've got a SQL S

[barryrwv]

Hello Everyone,

I've got a SQL Server 2000 table that contains typical contact information. Each unique contact in the table has a unique visitor#. I need to remove all duplicate records of the same visitor# while preserving the colums that are not being evaluated as unique. The end result is to have only one record for each contact. Does anyone know how to remove the duplicate records based on one field only, while preseving the remaining fields? Thank You

 

[longneck]

do you want to just remove the duplicates?

or do you need to combine the data on the records? what do you do if two records have two different values in the same field, such as two records with different home phone numbers?

 

[tlbroadbent]

You should really evaluate your data as longneck suggested to determine what to do if valid data exists in more than one row. If you don't care which set of values are chosen, you could use aggregation functions as in the following example.

Select VisitorNo, Max(col2), Max(col3), ..., Max(colN)
From TableName
Group By VisitorNo

You could also choose the complete row with the latest ID or update date if this data is available. Or select the row with the least number of null columns. Eliminating duplicates may not be a simple task. Make sure you add a unnique constraint to the table to avoid duplicate records in the future. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[kutty007]

Hi!

THis will work, if you want to retain the first record of each unique visitor and delete all subsequent ones.

Check out

Regards

Karthik

--==Query
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VISITOR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VISITOR]
GO

CREATE TABLE VISITOR (VName char(20), VDate Datetime, VNo int, VPK INT IDENTITY (1,1))
GO

INSERT INTO VISITOR (VName, VDate, VNO) values ('KARTHIK','01 MAR 2003',12)
INSERT INTO VISITOR (VName, VDate, VNO) values ('KARTHIK','01 MAR 2003',12)
INSERT INTO VISITOR (VName, VDate, VNO) values ('KUMAR','01 MAR 2003',11)
INSERT INTO VISITOR (VName, VDate, VNO) values ('KALA','01 MAR 2003',13)
INSERT INTO VISITOR (VName, VDate, VNO) values ('SURESH','01 MAR 2003',10)
INSERT INTO VISITOR (VName, VDate, VNO) values ('SURESH','01 MAR 2003',10)
INSERT INTO VISITOR (VName, VDate, VNO) values ('KALA','01 MAR 2003',13)
INSERT INTO VISITOR (VName, VDate, VNO) values ('SURESH','01 MAR 2003',10)
INSERT INTO VISITOR (VName, VDate, VNO) values ('KUMAR','01 MAR 2003',11)
Go


DELETE VISITOR
FROM (SELECT * FROM VISITOR) AS A
WHERE VISITOR.VName = A.VName AND
A.VPK < VISITOR.VPK

SELECT * FROM VISITOR