quote:

---

Originally posted by SM_SQL_newbie

Hi Hoep this will help you,
1.Method 1:

DELETE from (
(select * from Supplies)
MINUS
(select product_number,max(obligated_amount),max(water_amount),max(air_amount)
from Supplies
group by product_number))

2.Method 2:

DELETE from
select product,obligated_amount,water_amount,air_amount,rank() over (partition by product_number order by obligated_amount+water_amount_air_amount desc ) rank
from supplies S
where rank >1


Thanks

---

;with supplies (Product_number, Obligated_amount, Water_amount, Air_amount)
as
(
select 11515, 0, 0, 15
union all select 11515, 0, 0, 23
union all select 11515, 4524, 4524, 0
union all select 11515, 0, 25, 0
union all select 11515, 0, 15, 15
)

select	Product_number 
	,Obligated_amount 
	,Water_amount 
	,Air_amount
from	(
	select s.*
		,row_number() over (order by s.obligated_amount, s.water_amount, s.air_amount) as tieBreaker
	from	(
		select product_number
			,max(amount) maxAmt
		from supplies
		unpivot (amount for cols in (Obligated_amount, Water_amount, Air_amount)) u
		group by product_number
		) d
	join	supplies s on s.product_number = d.product_number
	where	s.Obligated_amount = d.maxamt
	or	s.Water_amount = d.maxamt
	or	s.Air_amount = d.maxamt
	) d
where	tieBreaker = 1

;With CTE 
AS
(
SELECT ProductID,
MAX(CASE WHEN Obligated > water THEN CASE WHEN Obligated > air THEN 1 ELSE 3 END ELSE 2 END) AS ID,
MAX(CASE WHEN Obligated > water THEN CASE WHEN Obligated > air THEN Obligated ELSE air END ELSE water END) AS Value
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Obligater DESC) AS OSeq,
       ROW_NUMBER() OVER (PARTITION BY Product ORDER BY water DESC) AS wseq,
       ROW_NUMBER() OVER (PARTITION BY Product ORDER BY air DESC) AS aseq, *
FROM table
)t
WHERE OSeq = 1 OR wseq=1 OR seq=1
GROUP BY ProductID
)

DELETE t
FROM table t
WHERE NOT EXISTS (SELECT 1 FROM CTE WHERE ProductID = t.ProductID
                  AND ((ID = 1 AND Obligated = Value)
                  OR (ID = 2 AND water = Value) 
                  OR (ID = 3 AND air = Value))
                 )

quote:

---

Originally posted by TG

>>shouldnt that be this?
could be. but still doesn't address the true dupe issue. when there are say 3 rows all with the same values across the row then you can either leave them all or delete them all. but there's no way to distinguish one from another (for the actual delete).

Be One with the Optimizer
TG

---