crazy statement :) please help

[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 and the difference between date2 is less than 1 minute.

Is that possible?

Thanks in advance

[Delerna]

is something like this what you are after

DELETE 
FROM TableName 
WHERE DATEDIFF(mi,Date1,Date2)<1
 

hope it helped

[lebanese007]

is something like this what you are after

DELETE 
FROM TableName 
WHERE DATEDIFF(mi,Date1,Date2)<1
 

hope it helped

Thanks Delerna,
here you are comparing date1 and date2, what i need is to compare date2 from one record and date 2 from another record.

[ck9663]

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 and the difference between date2 is less than 1 minute.

Is that possible?

Thanks in advance

assumption: there are only two records. if this assumption is wrong, this query will not work:

1. add a new column on your table C(1)
2. update mytable
set newcolumn = '1'
from
(select varchar1, mindate
(select varchar1, min(Date2) as mindate from yourtable where int1 = 1 group by varchar1 ) FirstRow inner join
(select varchar1, max(Date2) as maxdate from yourtable where int1 = 1 group by varchar1 ) SecondRow on FirstRow.varchar1 = SecondRow.varchar1
where abs(datediff(mi, mindate, maxdate)) < 1) summary
where mytable.varchar1 = summary.varchar1 and mytable.date2 = summary.mindate and int1 = 1

3. select * from mytable where newcolumn = '1'. inspect if you flag the right rows.
4. delete from mytable where newcolumn = '1'

you did this because if your query failed, you might delete the wrong row and you don't have a backup table. if you have to rerun your query, you have to replace back all fields to NULL or '0' to reset it.

-- ck

[ck9663]

and by the way, there's always cursor

-- ck