Question: I have a table that persists a lot of questions, each question belongs to a section:

```
Id      Question        SectionId
 1      What is ...     3
 2      Who is...       3
 3      When is...      2
 4      Why is...       1
 5      How is...       3

```

There is like 1000 questions, and around 50 sections. However, my query is simple, I select a given number of questions from the table from specific sections, for example

```
SELECT TOP 10 [Id], [Question] FROM [Questions] 
WHERE [SectionId] IN (1,2)
ORDER BY NEWID()

```

This is simple and working fine, except that sometimes I get 5 questions out of the requested 10 from a section that has only 6 questions, and 2 from a section that has 100 questions, and 3 from a section that has 20 questions. 

How can I make the result "proportional" with the number of the questions in each section. For example if I request 10 questions, I get more questions from the section that has more questions, and less questions from the sections with less questions. 

The only I can think of currently is to make multiple queries, first one to get the number of questions in each section, then do some math and decide how many questions from each section, and then make another few queries to get the number of questions as I want. This sound intensive and I hope there's a more practical way.

Note: An SQL query, or EF Linq query would work.

Answer: 
I can't think of a way to do this in a single step, unless you know in advance the number of sections and the proportions of each.

If these values have to be calculated at query time, you will need to run a query to get the sections and proportions and use that to build a Dynamic SQL query.

Use a GROUP BY query to get the SectionIDs and the number of questions in each Section, filtered by the Sections you want to include.

Iterate through that result to build a dynamic UNION ALL query that gets a TOP n (calculate n based on the percentage of the Section's Count / Total Count) of questions for each Section (one query per section), so that you end up dynamically building something that looks something like this:

```
SELECT TOP 5 ID, Question --because SectionID 1 is 50% of the questions
FROM Questions
WHERE SectionID=1
ORDER BY NEWID()
UNION ALL 
SELECT TOP 3 ID, Question  --because SectionID 2 is 30% of the questions
FROM Questions
WHERE SectionID=2
ORDER BY NEWID()
UNION ALL 
SELECT TOP 2 ID, Question  --because SectionID 3 is 20% of the questions
FROM Questions
WHERE SectionID=3
ORDER BY NEWID()

```

Another approach you could think about is to create an artificial ranking column that is factored by the relative density of the section.

What I mean, for example (super simplifying it) is suppose Section 1 was 75% of the questions, and Section 2 was 25%.

You'd use ROW\_NUMBER(), partitioned by `SectionID`, ordered by NEWID() and factored so that:

Section 1 would have values like 1,2,3,5,6,7, etc (3 out of every 4 cardinal values)

Section 2 would have values like 1, 5, 9, 10 etc (1 out of every 4)

Then Order your query result by this artificial column.
You could select always 10% of your records in any sections with this:

```
SELECT TOP ( select CAST(( COUNT(*) * 0.1 ) AS INT ) 
FROM QUESTION WHERE SECTIONID IN ( 1,2)) * FROM QUESTION 
WHERE [SectionId] IN (1,2)
ORDER BY NEWID()

```