Originally Posted by lebanese007

Hi all,
This is my first time... and i'm not an sql guy by any means, but i can insert, update, blah blah...
I have a question to ask, i have a table that has
UniqueInt | Date1 | int1 | Date2 | varchar 1

I have duplicate records that i need to delete and i need to write an sql statement.

it goes like this,

UniqueInt | Date1 | int1 | Date2 | varchar 1
1 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:00:00 am| 123456
2 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:30:00 am| 123456

delete the first record where int1 =1 and varchar1 is the same in both records and the difference between date2 in both records is less than 1 minute.

I know how to do the less than a minute "(DATEDIFF(minute, field1, field2) < 1)" within one record, but i don't how to do compare the same field name in 2 records

Is that possible?

Thanks in advance

Originally Posted by Zadj

Hi lebanese007,

If you are wanting a script that you can run to clean up the data in the table you can try the solution below. If this is an ongoing issue then it may be worth looking at putting a check during the insert to stop these additional rows. This may not be the best performance if the inserts are very frequent.

Anyway, here is a sample solution. I have simplified the table to the relevant parts only.

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

CREATE TABLE #test
(ID INT,
val INT,
txt VARCHAR(50),
dt DATETIME)
 
INSERT INTO #test
VALUES (1, 10, 'a', '2008-1-31 12:00:00')
INSERT INTO #test
VALUES (2, 10, 'a', '2008-1-31 12:00:30')
INSERT INTO #test
VALUES (3, 11, 'b', '2008-1-31 12:00:00')
INSERT INTO #test
VALUES (4, 10, 'a', '2008-1-31 12:02:00')
 
SELECT * FROM #test
 
DELETE FROM later --Delete the later result
FROM 
--Join the table to itself where the values match
#test later INNER JOIN
#test ealier ON later.val = ealier.val and later.txt = ealier.txt
WHERE
-- make sure the ones in the ealier table are before the ones in the later table (stops the rows matching themselves)
ealier.dt < later.dt AND
-- make sure the later date is less(or equal) than one minute from the earlier date
later.dt <= DATEADD(minute,1, ealier.dt)
 
 
SELECT * FROM #test
 
DROP TABLE #test

create table #test (ID INT, val INT, txt VARCHAR(50), dt DATETIME) Insert into #test Values (1, 10, 'a', '2008-1-31 12:00:00') Insert into #test Values (2, 10, 'a', '2008-1-31 12:00:30') Insert into #test Values (3, 11, 'b', '2008-1-31 12:00:00') Insert into #test Values (4, 10, 'a', '2008-1-31 12:02:00') Select * from #test DELETE FROM later --Delete the later result FROM --Join the table to itself where the values match #test later INNER JOIN #test ealier ON later.val = ealier.val and later.txt = ealier.txt WHERE -- make sure the ones in the ealier table are before the ones in the later table (stops the rows matching themselves) ealier.dt < later.dt AND -- make sure the later date is less(or equal) than one minute from the earlier date later.dt <= DATEADD(minute,1, ealier.dt) Select * from #test Drop table #test

Let me know how you get on with that.

Zadjil

Originally Posted by serkansendur

by the way, my code erases all the rows having duplicate values in varchar1 column and value of 1 for the int1 column. if you want to delete the first row, then you have to set a break statement in the cursor as follows :


begin

delete top (1) from mytable where (@varchar1=varchar1 and int1=1)
break
FETCH NEXT FROM mycursor INTO @varchar1
end

Originally Posted by lebanese007

Hi serkan
can you please use pure sql, i don't know what my cursor means.

Thanks in advance