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

Question

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?

Answer 1

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.