Take the 2-minute tour ×
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. It's 100% free, no registration required.

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? –  FreshPhilOfSO 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
add comment

1 Answer

up vote 0 down vote accepted
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.

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. –  AlexKuznetsov Oct 25 '12 at 21:44
add comment

Your Answer

 
discard

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.