Removing Duplicate records.. - 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

SQL Server Development (2000)

Removing Duplicate records..

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2007-02-22 : 12:40:43

Hi all

I have a query ...

select CONTRACT_SITE_ID, FAMILY, CONTRACT_PARTY_TYPE_CODE, count(*)
from CONTRACT_PARTY
group by CONTRACT_SITE_ID, FAMILY, CONTRACT_PARTY_TYPE_CODE
having count(*) = 2

output:

10000403127 INVBN BILL 2
10000403127 INVBN DELCO 2
10000403127 INVBN FUFIL 2
10000410422 REVSR BILL 2
10000410422 REVSR DELCO 2
10000410422 REVSR FUFIL 2

The table contract_party has a primary key column CONTRACT_PARTY_ID,

Now i need a query to delete one of the two records (Remove the one with smaller CONTRACT_PARTY_ID) that matches the above criteria.

Thanks..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-22 : 12:58:02

Try this and if it is giving the required output, you can convert it to DELETE. Remember, this SELECT will give you the records which you need to delete.

Select t1.*
From CONTRACT_PARTY t1
JOIN 
(Select CONTRACT_SITE_ID, Max(CONTRACT_PARTY_ID) as CONTRACT_PARTY_ID
 From CONTRACT_PARTY
 Group by CONTRACT_SITE_ID) t2
on t1.CONTRACT_SITE_ID = t2.CONTRACT_SITE_ID
and t1.CONTRACT_PARTY_ID < t2.CONTRACT_PARTY_ID

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources