query to extract random rows from a table

Question

I have the following 2 tables

Table 1 - Questions
Contains questions and marks allotted for each questions

ID| Questions                    | Marks
________________________________________
1 | What is your name?           |  2
2 | How old are you?             |  2
3 | Where are you from?          |  2
4 | What is your father's name?  |  2
5 | Explain about your project?  |  5
6 | How was the training session?|  5

Table 2 - Question Format
Contains how many questions (count) to be extracted for a set of Marks

Mark  | Count
-------------
  2   |    2
  5   |    1

I want the random questions to be picked up from the table [Questions] as per the [count] set in the table [Question_Format].

 ID |     Question    
 ----------------------------
 2  |   How old are you?             
 3  |   Where are you from? 
 6  |   How was the training session?

Answer 1

Here is the idea. Enumerate the questions for each "mark" by using row_number(). Then use this sequential number to select the random questions:

select q.*
from (select q.*,
             row_number() over (partition by marks order by newid()) as seqnum
      from questions q
     ) q join 
     marks m
     on q.marks = m.mark and q.seqnum <= m.count;

Answer 2

with cte as (
    select *, row_number() over(partition by Marks order by newid()) as rn
    from Questions
)
select
    q.id, q.Questions
from cte as q
    inner join QuestionFormat as qf on qf.Mark  = q.Marks
where q.rn <= qf.[Count]

sql fiddle demo