SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Removing earliest entry of duplicate field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

langer5247
Starting Member

2 Posts

Posted - 04/28/2006 :  12:23:52  Show Profile  Reply with Quote
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

USA
938 Posts

Posted - 04/28/2006 :  13:46:15  Show Profile  Reply with Quote
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

Edited by - nathans on 04/28/2006 14:21:47
Go to Top of Page

langer5247
Starting Member

2 Posts

Posted - 04/28/2006 :  14:29:21  Show Profile  Reply with Quote
Thanks Nathan.

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

This was it right there.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000