## TOP 25% rows by Weightage

 Author Message chintan.floydian SSC Rookie Group: General Forum Members Points: 45 Visits: 32 I have a table as below. I need to find the members which are in the bottom quartile (25%) of the weights in each class`ClassID MemberID Weight A 1 10A 2 20A 3 40A 5 25A 6 15A 8 10B 1 50B 2 10B 3 60B 6 400`For above example, total weight of class A is 120. If I order by weight and pick the top ones such that their summed weight comprise less than or equal to 25% of total weight (25% of 120 = 30 for class A)I would pick Members 1 and 8. If I include member 6, it would exceed 25% (30)The output I need is`ClassID MemberID Weight A 1 10A 8 10B 1 50B 2 10B 3 60`What would be the best way of achieving this in TSQL (Sql server 2008)?Hope the question is clear Sean Lange SSC Guru Group: General Forum Members Points: 149265 Visits: 18575 You can do this using a running total. Take a look at this article that explains how to do a running total in sql.http://www.sqlservercentral.com/articles/T-SQL/68467/If you get stuck feel free to post back here. It is best if you can provide data in a consumable format. Since you are brand new around here I did this for you so you can see.`;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)` _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Modens splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) Mark Cowne SSChampion Group: General Forum Members Points: 14794 Visits: 26578 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,WeightFROM CTEWHERE RunningTotalWeight <= TotalWeight/4ORDER BY ClassID,MemberID;` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 chintan.floydian SSC Rookie Group: General Forum Members Points: 45 Visits: 32 Thanks very much guys...Unfortunately I'm still on 2008I'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,WeightSELECT d.classid,d.memberid,d.WeightFROM Data dINNER 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 Sean Lange SSC Guru Group: General Forum Members Points: 149265 Visits: 18575 chintan.floydian (4/18/2013)Thanks very much guys...Unfortunately I'm still on 2008I'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,WeightSELECT d.classid,d.memberid,d.WeightFROM Data dINNER 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 appreciatedThere are a few things you would have to do differently here for the quirky update to work. You would need to move this to a table instead of a cte. You need to have a clustered index as described in the article. The idea here is that you could use the quirky update to get your running total and then select rows where the total column is less than max of that column * 25. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Modens splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)