Remove Sequential Duplicate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Remove Sequential Duplicate

Author

Topic

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 09:29:32

Hi,
ID Date Value
59 2005-11-09 00:00:00 6.5180
59 2005-11-16 00:00:00 6.5180
59 2005-11-23 00:00:00 6.5180
59 2005-11-30 00:00:00 6.5180
59 2005-12-07 00:00:00 6.5180
59 2005-12-14 00:00:00 6.5180
59 2005-12-21 00:00:00 3.6600
59 2005-12-28 00:00:00 3.6033
59 2006-01-04 00:00:00 3.5793
59 2006-01-11 00:00:00 3.5544
59 2006-01-18 00:00:00 3.4872

I need delete marked values and the same pattern will appear any where on table and any times.I need find out these duplicares and need to delete

thanks

Hillside

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 16:23:20

It is not simple stright filtering the duplicates.We have to look datewise than find duplicate than remove it.

I think no one is understood my question or.....??

Thanks in advance for help.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 16:29:36

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 16:39:39

You are Brilliant...thanks

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 16:58:58

Dinkar Thanks for quick reply.

If i add 2 more rows which you can see in green color and I dont want these to deleted and only last one has to get delete.

Presently if i use same query it will delete both the rows

Declare @t table (ID int, Date datetime, Value decimal(10,5) )
insert into @t
Select 59, '2005-11-09 00:00:00' ,6.5180 union all
Select 59, '2005-11-16 00:00:00' ,6.5180 union all
Select 59, '2005-11-23 00:00:00' ,6.5180 union all
Select 59, '2005-11-30 00:00:00' ,6.5180 union all
Select 59, '2005-12-07 00:00:00' ,6.5180 union all
Select 59, '2005-12-14 00:00:00' ,6.5180 union all
Select 59, '2005-12-21 00:00:00' ,3.6600 union all
Select 59, '2005-12-28 00:00:00' ,3.6033 union all
Select 59, '2006-01-04 00:00:00' ,3.5793 union all
Select 59, '2006-01-11 00:00:00' ,3.5544 union all
Select 59, '2006-01-18 00:00:00' ,3.4872 union all
Select 59, '2006-01-11 00:00:00' ,6.5180 union all
Select 59, '2006-01-18 00:00:00' ,6.5180 union all
Select 59, '2006-01-25 00:00:00' ,3.5888

Select * from @t

Delete T
from @t T
Join (
Select id, min(date) Mdate, value
from @t
Group by id, value
) T2 on t.ID = T2.id And T.Value = T2.value and T.Date > T2.Mdate
Select * from @t

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 17:01:55

Sorry date was wrong in the earlier reply.

Select 59, '2005-11-09 00:00:00' ,6.5180 union all
Select 59, '2005-11-16 00:00:00' ,6.5180 union all
Select 59, '2005-11-23 00:00:00' ,6.5180 union all
Select 59, '2005-11-30 00:00:00' ,6.5180 union all
Select 59, '2005-12-07 00:00:00' ,6.5180 union all
Select 59, '2005-12-14 00:00:00' ,6.5180 union all
Select 59, '2005-12-21 00:00:00' ,3.6600 union all
Select 59, '2005-12-28 00:00:00' ,3.6033 union all
Select 59, '2006-01-04 00:00:00' ,3.5793 union all
Select 59, '2006-01-11 00:00:00' ,3.5544 union all
Select 59, '2006-01-18 00:00:00' ,3.4872 union all
Select 59, '2006-01-25 00:00:00' ,6.5180 union all
Select 59, '2006-02-01 00:00:00' ,6.5180 union all
Select 59, '2006-02-08 00:00:00' ,3.5888

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-06 : 17:02:51

Try to change the last sample date value to 6.5180 too! It is gone.
You need another approach.

What is your primary key on this table? Do you have a clustered index?

If so, you can use the

update x set @y = y = case when ... then ... else ... end

method.

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 17:08:49

And why would you want to keep one of those 2 rows? Is there aby business logic? For a given value of Id and value (59, 6.5180) how many rows can you have? if you have multiple which ones do you want deleted?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 17:09:22

Thanks for reply.

There is no primary key on this table and there is only forien key value on this table.

I would realy thankful if could please explain me in little detail how to tackle this problem.

thanks

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 17:12:19

Dinakar,

I data of table depends on date and In the 2 new rows which i have added are 1 is orginal data and second is dupm value which need to removed.

What we did in the past was if we dont get value on particular date we used have previous week data.

Waiting for your reply.
thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 17:12:39

LEts say you have these rows:


Select 59, '2005-11-09 00:00:00' ,6.5180 union all
Select 59, '2005-11-16 00:00:00' ,6.5180 union all
Select 59, '2005-11-23 00:00:00' ,6.5180 union all
Select 59, '2005-11-30 00:00:00' ,6.5180 union all
Select 59, '2005-12-07 00:00:00' ,6.5180 union all
Select 59, '2005-12-14 00:00:00' ,6.5180 union all
Select 59, '2006-01-25 00:00:00' ,6.5180 union all 
Select 59, '2006-02-01 00:00:00' ,6.5180 union all

which ones do you need to keep?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 17:15:38

Only top one should retained and rest should be deleted.but In case if any other dates comes in between these range than we cant delete all these data.

Hillside
Starting Member

23 Posts

Posted - 2007-08-06 : 17:17:17

I mean if these dates are continous with week difference i need to keep only first week data rest should be deleted beacuse they are all dump data.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 17:53:21

[code]
Declare @t table (ID int, Date datetime, Value decimal(10,5) )
insert into @t

Select 59, '2005-11-09 00:00:00' ,6.5180 union all
Select 59, '2005-11-16 00:00:00' ,6.5180 union all
Select 59, '2005-11-23 00:00:00' ,6.5180 union all
Select 59, '2005-11-30 00:00:00' ,6.5180 union all
Select 59, '2005-12-07 00:00:00' ,6.5180 union all
Select 59, '2005-12-14 00:00:00' ,6.5180 union all
Select 59, '2005-12-21 00:00:00' ,3.6600 union all
Select 59, '2005-12-28 00:00:00' ,3.6033 union all
Select 59, '2006-01-04 00:00:00' ,3.5793 union all
Select 59, '2006-01-11 00:00:00' ,3.5544 union all
Select 59, '2006-01-18 00:00:00' ,3.4872 union all
Select 59, '2006-01-25 00:00:00' ,6.5180 union all
Select 59, '2006-02-01 00:00:00' ,6.5180 union all
Select 59, '2006-02-08 00:00:00' ,3.5888

Delete TF
From @t Tf
Join (
Select T.ID , T.Date, t.Value , col4 = (select isnull(datediff(wk, T.date, min(T2.date)),1) From @t T2 Where T2.ID = T.ID and T.Value = T2.Value And T2.Date > T.Date )
from @t T
Where (select isnull(datediff(wk, T.date, min(T2.date)),0) From @t T2 Where T2.ID = T.ID and T.Value = T2.Value And T2.Date > T.Date ) <= 1
And T.Date > (Select min(date) from @t T2 Where T2.ID = T.ID and T.Value = T2.Value )
) T4 on TF.ID = T4.id and TF.Date = T4.date And Tf.Value = T4.value

Select * from @t

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-06 : 18:10:36

ID Date Value
59 2005-12-14 00:00:00.000 6,518

is still there...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-06 : 18:23:39

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID INT, Date DATETIME, [Value] MONEY)

INSERT @Sample
SELECT 59, '2005-11-09 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2005-11-16 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2005-11-23 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2005-11-30 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2005-12-07 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2005-12-14 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2005-12-21 00:00:00' ,3.6600 UNION ALL
SELECT 59, '2005-12-28 00:00:00' ,3.6033 UNION ALL
SELECT 59, '2006-01-04 00:00:00' ,3.5793 UNION ALL
SELECT 59, '2006-01-11 00:00:00' ,3.5544 UNION ALL
SELECT 59, '2006-01-18 00:00:00' ,3.4872 UNION ALL
SELECT 59, '2006-01-25 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2006-02-01 00:00:00' ,6.5180 UNION ALL
SELECT 59, '2006-02-08 00:00:00' ,3.5888

-- Stage the data
DELETE s0
FROM @Sample AS s0
LEFT JOIN (
SELECT d.ID,
MIN(d.Date) AS Date
FROM (
SELECT s1.ID,
s1.Date,
(SELECT MIN(s2.Date) FROM @Sample AS s2 WHERE s2.ID = s2.ID AND s2.Date > s1.Date AND s2.Value <> s1.Value) AS theDate
FROM @Sample AS s1
) AS d
GROUP BY d.ID,
d.theDate
) AS q ON q.ID = s0.ID AND q.Date = s0.Date
WHERE q.ID IS NULL

-- Show the expected output
SELECT ID,
Date,
Value
FROM @Sample
ORDER BY ID,
Date[/code]

E 12°55'05.25"
N 56°04'39.16"

Hillside
Starting Member

23 Posts

Posted - 2007-08-07 : 04:28:51

Thank you very much for your great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources