Removing Dupes Based on a Condition

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 2005 Forums

Transact-SQL (2005)

Removing Dupes Based on a Condition

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-02 : 08:50:23

Maxim writes "I got a table MyTable ([ID] INT, Serial INT, SerialDate DATETIME). I need to delete all duplicate entries in Serial field except for ONE with the earliest date. E.g.

ID, Serial, SerialDate
1, 111, 2007-01-01
2, 222, 2007-01-03
3, 333, 2007-01-09
4, 222, 2007-01-02

I need to delete the row with [ID] 2 and keep the row with [ID] 4 because Serial in 2 is the same as Serial in 4, but the date in 4 is earlier.

Thanks!"

B0g
Starting Member

19 Posts

Posted - 2007-03-02 : 09:18:58

Try this... it migth work:

DELETE
FROM MyTable
WHERE id IN (
SELECT id FROM MyTable T
WHERE Serial IN (SELECT Serial FROM MyTable GROUP BY Serial HAVING COUNT(1) > 1)
AND SerialDate NOT IN (SELECT MIN(SerialDate) FROM MyTable WHERE Serial = T.Serial))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-02 : 09:37:22

[code]
declare @table table
(
[ID] int,
Serial int,
SerialDate datetime
)
insert into @table
select 1, 111, '2007-01-01' union all
select 2, 222, '2007-01-03' union all
select 3, 333, '2007-01-09' union all
select 4, 222, '2007-01-02'

delete t
from @table t
where t.ID <> (select top 1 [ID] from @table x where x.Serial = t.Serial order by SerialDate)

select *
from @table

/*
ID Serial SerialDate
----------- ----------- -----------
1 111 2007-01-01
2 222 2007-01-03
3 333 2007-01-09
*/
[/code]

B0g
Starting Member

19 Posts

Posted - 2007-03-02 : 09:48:40

quote:

Originally posted by khtan


delete	t
from	@table t
where	t.ID	<> (select top 1 [ID] from @table x where x.Serial = t.Serial)

delete t
from @table t
where t.ID <> (select top 1 [ID] from @table x where x.Serial = t.Serial ORDER BY SerialDate ASC)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-02 : 09:52:01

quote:
Originally posted by B0g

quote:
Originally posted by khtan

delete t
from @table t
where t.ID <> (select top 1 [ID] from @table x where x.Serial = t.Serial)

KH

delete t
from @table t
where t.ID <> (select top 1 [ID] from @table x where x.Serial = t.Serial ORDER BY SerialDate ASC)

Thanks. Missed that order by statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources