How do I select TOP 5 PERCENT from each group?

Question

I have a sample table like this:

CREATE TABLE #TEMP(Category VARCHAR(100), Name VARCHAR(100))

INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'John')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Adam')
INSERT INTO #TEMP VALUES('A', 'Lisa')
INSERT INTO #TEMP VALUES('A', 'Lisa')
INSERT INTO #TEMP VALUES('A', 'Bucky')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Lily')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Tom')
INSERT INTO #TEMP VALUES('B', 'Ross')
INSERT INTO #TEMP VALUES('B', 'Ross')
INSERT INTO #TEMP VALUES('B', 'Ross')

SELECT Category, Name, COUNT(Name) Total
FROM #TEMP
GROUP BY Category, Name
ORDER BY Category, Total DESC

DROP TABLE #TEMP

Gives me the following:

A   John    6
A   Adam    4
A   Lisa    2
A   Bucky   1
B   Lily    5
B   Tom     4
B   Ross    3

Now, how do I select the TOP 5 PERCENT records from each category assuming each category has more than 100 records (did not show in sample table here)? For instance, in my actual table, it should remove the John record from A and Lily record from B as appropriate (again, I did not show the full table here) to get:

A   Adam    4
A   Lisa    2
A   Bucky   1
B   Tom     4
B   Ross    3

I have been trying to use CTEs and PARTITION BY clauses but cannot seem to achieve what I want. It removes the TOP 5 PERCENT from the overall result but not from each category. Any suggestions?

May help in a small way - If you have a count for a group, remember that 5 percent would be "row_num <= (5 * count) / 100" — Kieren Johnstone, Sep 28 '11 at 07:52
@KierenJohnstone: +1 Thank you. I know I might have to use CROSS APPLY or something similar but still having some trouble. Will update if I figure it out. — Legend, Sep 28 '11 at 07:56
What is the desired output then please? Remove top 5 percent percent is very little compared to a count of 6. One row (A, John) is 16%. — gbn, Sep 28 '11 at 07:57
See this similar question: http://stackoverflow.com/questions/4373451/select-top-n-percent-by-year — ypercubeᵀᴹ, Sep 28 '11 at 07:57
@gbn: Sorry! I could not add 100 records to the sample table. I updated my question to have the assumption that I have more than 100 records in each category. I put the output I am expecting but of course this won't work on the sample table. — Legend, Sep 28 '11 at 07:59
@Legend - Still not clear to me what you want. Please give desired results and explain how they are arrived at. Not sure at what point you want the `TOP 5%` applied. — Martin Smith, Sep 28 '11 at 08:03
@MartinSmith: Updated my question with an example output. I want the `TOP 5 PERCENT` to be applied **after** calculating the counts. — Legend, Sep 28 '11 at 08:05
I'm not sure if I understand you right, but it's going to be very hard to remove some rows but not others if they don't have unique fields. I.e. let's suppose there are 100 `A, John` records; you can only remove all or none of them, not the top 5%. — Tim Rogers, Sep 28 '11 at 08:06

score 21 · Accepted Answer · edited Sep 28 '11 at 08:09

21

You could use a CTE (Common Table Expression) paired with the NTILE windowing function - this will slice up your data into as many slices as you need, e.g. in your case, into 20 slices (each 5%).

;WITH SlicedData AS
(
   SELECT Category, Name, COUNT(Name) Total,
            NTILE(20) OVER(PARTITION BY Category ORDER BY COUNT(Name) DESC) AS  'NTile'
   FROM #TEMP
   GROUP BY Category, Name
)
SELECT *
FROM SlicedData
WHERE NTile > 1

This basically groups your data by Category,Name, orders by something else (not sure if COUNT(Name) is really the thing you want here), and then slices it up into 20 pieces, each representing 5% of your data partition. The slice with NTile = 1 is the top 5% slice - just ignore that when selecting from the CTE.

See:

for more info

edited Sep 28 '11 at 08:09

Legend

113,822
119
272
400

answered Sep 28 '11 at 08:04

marc_s

732,580
175
1,330
1,459

2

This serves my purpose. Thanks a million. I fixed your post for some missing parts in the query to make it run out of the box. – Legend Sep 28 '11 at 08:09
@Legend I thought you wanted to remove records, not just select them? – Tim Rogers Sep 28 '11 at 08:12
@TimRogers: Sure. I just made an exclusion list using this query for the names that I wanted to remove. I will try to fix my question. – Legend Sep 28 '11 at 08:14

score 3 · Answer 2 · edited Oct 01 '12 at 20:29

select Category,name,CountTotal,RankSeq,(50*CountTotal)/100 from (
select Category,name,COUNT(*)
over (partition by Category,name ) as CountTotal,
ROW_NUMBER()
over (partition by Category,name order by Category) RankSeq from #TEMP
--group by Category,Name 
) temp
where RankSeq <= ((50*CountTotal)/100)
order by Category,Name,RankSeq

Output:

Category    name     CountTotal RankSeq     50*CountTotal)/100
A           Adam     4          1           2
A           Adam     4          2           2
A           John     6          1           3
A           John     6          2           3
A           John     6          3           3
A           Lisa     2          1           1
B           Lily     5          1           2
B           Lily     5          2           2
B           Ross     3          1           1
B           Tom      4          1           2
B           Tom      4          2           2

I hope this helps :)

score 1 · Answer 3 · edited Nov 29 '17 at 10:27

1

;WITH SlicedData AS
(
   SELECT Category, Name, COUNT(Name) Total,
            **PERCENT_RANK() OVER(PARTITION BY Category ORDER BY COUNT(Name) DESC) * 100** AS  'Percent'
   FROM #TEMP
   GROUP BY Category, Name
)
SELECT *
FROM SlicedData
WHERE Percent < 5

NTile will not work if number of records is less than your tile number.

edited Nov 29 '17 at 10:27

Vega

27,856
27
95
103

answered Nov 29 '17 at 10:05

Raghu Rajendran

21
3

How do I select TOP 5 PERCENT from each group?

3 Answers3

Linked