# How do I query based on percentages of a subset of values?

I have a table with a column named `Type` that can have some set of values. For instance, A, B, C, D, etc. Behind the scenes this is mapped to an Enum.

I need to query this table to get a percentage of some subset of the values.

For example, I may want to get 400 total rows where 25 percent are `Type = 'A'`, 25 percent are `Type = 'B'`, and 50 percent are 'Type = 'C'`. If the total quantity of rows is not 400 it should return up to 400. Ideally, this would return 100`A`rows, 100`B`rows and 200`C` rows.

Or, perhaps, I want 100 rows where 50 percent are `A` and 50 percent are `C`. Etc.

If it makes the query easier to write, I can pass in a count of each value I want instead of percentage, e.g.: 200 of A, 200 of C.

What is the best way to perform this query?

-
What order do you want these returned in if there's more than 400? – Phil Sep 6 '12 at 19:22
Order does not matter, as long as the amounts match the percentages as closely as possible. – AceJordin Sep 6 '12 at 19:27
Will the percentages be hard coded or passed in as variables? – Mr.Brownstone Sep 6 '12 at 19:42
Not hard coded. It needs to be dynamic. So yes, passed in as variables. Ex: one execution could be 100% A. Another 50% B, 12% D and 38% E. – AceJordin Sep 6 '12 at 19:53

``````WITH  RequiredAmounts
AS ( SELECT 'A' AS [TYPE] ,
200 AS Amount
UNION ALL
SELECT 'B' ,
200
)
SELECT  t.*
FROM    RequiredAmounts
CROSS APPLY ( SELECT TOP ( RequiredAmounts.Amount )
*
FROM    YourTable AS t
WHERE   t.[TYPE] = RequiredAmounts.[Type]
) AS t
``````

RequiredAmounts can select from a temporary table, table variable, a TVP. It can be anything. Just replace this sample CTE with whatever suits your needs.

-
The WITH syntax along with UNION ALL is something I hadn't thought of. Thanks. But it's not dynamic; the query would have to change to select only one type or 3 types, etc. – AceJordin Sep 12 '12 at 23:13
@AceJordin RequiredAmounts can select from a temporary table, table variable, a TVP, anything. Just replace it with whatever suits your needs. – A-K Oct 25 '12 at 21:44