Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 100Arows, 100Brows and 200C` 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?

share|improve this question
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
up vote 0 down vote accepted
WITH  RequiredAmounts
        AS ( SELECT 'A' AS [TYPE] ,
                    200 AS Amount
             UNION ALL
             SELECT 'B' ,
  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.

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.