If you're using SQL Server 2012, you can do this

;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

),

CTE AS (

SELECT ClassID,MemberID,Weight,

SUM(Weight) OVER(PARTITION BY ClassID ORDER BY Weight) AS RunningTotalWeight,

SUM(Weight) OVER(PARTITION BY ClassID) AS TotalWeight

FROM Data)

SELECT ClassID,MemberID,Weight

FROM CTE

WHERE RunningTotalWeight <= TotalWeight/4

ORDER BY ClassID,MemberID;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537