chintan.floydian (4/18/2013)

---

Thanks very much guys...Unfortunately I'm still on 2008

I've tried the below and it works, but I'm not sure how to achieve this through the quirky update as advised in the article

;with Data(ClassID, MemberID, Weight) as

(

select 'A', 1, 10 union all

select 'A', 2, 20 union all

select 'A', 3, 40 union all

select 'A', 5, 25 union all

select 'A', 6, 15 union all

select 'A', 8, 10 union all

select 'B', 1, 50 union all

select 'B', 2, 10 union all

select 'B', 3, 60 union all

select 'B', 6, 400

)

--SELECT* FROM Data ORDER BY ClassID,Weight

SELECT d.classid,d.memberid,d.Weight

FROM Data d

INNER JOIN (

SELECT D2.ClassID,SUM(Weight) as total_weight

FROM Data D2

GROUP BY D2.ClassID

) Totals ON Totals.ClassID = d.ClassID

WHERE

(SELECT SUM(weight) FROM Data D3 where D3.ClassID = d.ClassID and D3.weight <= d.weight)

<= 0.25*Totals.total_weight

Help is much appreciated