Removing earliest entry of duplicate field

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

SQL Server Development (2000)

Removing earliest entry of duplicate field

Author

Topic

langer5247
Starting Member

2 Posts

Posted - 2006-04-28 : 12:23:52

How do I remove an entire record from the query where there is only one duplicate field, and i want to keep the earliest copy?
For example,

PrimaryID ForeignID Stage UpdatedDate
----------------------------------------------
1 70 Start 4/10/06 11:00:01
2 70 Middle 4/10/06 11:30:01
3 70 Middle 4/11/06 9:00:00
4 88 Start 4/20/06 10:23:00
5 88 Start 4/21/06 12:32:05
6 88 End 4/21/06 12:50:00

In this example I would want to remove rows with primary key's of 3 and 5 because they are in the same stage as prior records but are at a later date. I only want one of each stage for each foreignID and I want the earliest entry. "

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-28 : 13:46:15

This should point you in the right direction... post back here if you run into any difficulty.

declare @table table (primaryID int, ForeignID int, Stage varchar(10), UpdatedDate datetime)
insert into @table
	select	1, 70, 'Start', '4/10/06 11:00:01' union all
	select	2, 70, 'Middle', '4/10/06 11:30:01' union all 
	select	3, 70, 'Middle', '4/11/06 9:00:00' union all 
	select	4, 88, 'Start', '4/20/06 10:23:00' union all 
	select	5, 88, 'Start', '4/21/06 12:32:05' union all 
	select	6, 88, 'End', '4/21/06 12:50:00'
	
select * from @table

select ForeignID, Stage, Min(UpdatedDate) UpdatedDate
from @table
group by ForeignID, Stage
order by 1,3

Nathan Skerl

langer5247
Starting Member

2 Posts

Posted - 2006-04-28 : 14:29:21

Thanks Nathan.

select ForeignID, Stage, Min(UpdatedDate) UpdatedDate
from @table
group by ForeignID, Stage
order by 1,3

This was it right there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources