remove dups with comparing the values

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

General SQL Server Forums

New to SQL Server Programming

remove dups with comparing the values

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2010-03-24 : 15:29:06

Hi.I have a table with duplications in a field(TAG).
I want to remove some of these dups if the product_base_price is less than product_price on each dup.
which means if I have these values:

Product_code TAG product_price product_base_price
BCE1119 1033 4 5.75
BCE2040 1033 2.01 22.17

BCD1018 1538 11.97 17.35
BCC1015 1538 9.15 10.09

I dont want to remove any field of the tag 1033
but this line:
BCC1015 1538 9.15 10.09
has to remove because the product_base_price is 10.09(with product_code = BCC1015) is less than product_price on the other dup tag(product_code=BCC1015) which is 9.15

I'm really stuck cos I never compare @ different fields like this.
plz help
& thanks in advanced

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-25 : 03:37:46

I think this is what you need:

DECLARE @table table (
  Product_code varchar(30),
  TAG int,
  product_price decimal(18, 2),
  product_base_price decimal(18, 2)
  )
insert into @table
SELECT 'BCE1119', 1033, 4, 5.75 UNION ALL
SELECT 'BCE2040', 1033, 2.01, 22.17 UNION ALL
SELECT 'BCD1018', 1538, 11.97, 17.35 UNION ALL
SELECT 'BCC1015', 1538, 9.15, 10.09

select * from @table

--> See which data to delete:
select * 
from @table a
  inner join @table b
    on a.TAG = b.TAG 
where b.product_base_price < a.product_price

--> Do the actual delete:
delete b
from @table a
  inner join @table b
    on a.TAG = b.TAG 
where b.product_base_price < a.product_price

select * from @table

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:57:14

are you using sql 2005?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

a.ashabi
Posting Yak Master

117 Posts

Posted - 2010-03-25 : 12:33:40

its sql 2000
it worked
thank u so much Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources