locked
Select a column and assign it different percentages for its 2 rows RRS feed

  • Question

  • Hi there,

    I got a database named Quiz and i want to make a query based on Questions table.

    Questions table got "question id", "title", "difficulty" and "chapter" as columns.

    How is it possible to make a query that have just 20 questions selected and in that 20 questions,

    the 40% would be difficulty "easy"  and the rest 60% would be difficulty "hard".

    I tried to find a way for it and in the end i couldnt do it.

    I will appreciate it if you can help me.

    Thanks.

    Pantelis G.

    Wednesday, March 13, 2013 8:24 PM

Answers

  • Try

    ;with cte as (select *, row_number() over (partition by Difficulty order by NewId()) as RandomNumber from Questions)
    
    select * from cte where Difficulty = 'Easy' and RandomNumber <=8
    
    UNION ALL
    
    select * from cte where Difficulty = 'Hard' and RandomNumber < = 12


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by PantelisG Wednesday, March 13, 2013 10:18 PM
    Wednesday, March 13, 2013 8:32 PM

All replies

  • Try

    ;with cte as (select *, row_number() over (partition by Difficulty order by NewId()) as RandomNumber from Questions)
    
    select * from cte where Difficulty = 'Easy' and RandomNumber <=8
    
    UNION ALL
    
    select * from cte where Difficulty = 'Hard' and RandomNumber < = 12


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by PantelisG Wednesday, March 13, 2013 10:18 PM
    Wednesday, March 13, 2013 8:32 PM
  • Sorry to ask you but i see first time this line you just typed out. What should i do to accomplish that?

    I understand the 2 select phrases with union but i dont understand the first one :

    ;with cte as (select *, row_number() over (partition by Difficulty order by NewId()) as RandomNumber from Questions)

    What should i do with that?

    Wednesday, March 13, 2013 9:57 PM
  • This code creates common table expression and numbers questions randomly within level of difficulty, e.g.

    Question                                                 Difficulty  RandomNumber

     What is 2*2                                             Easy           1

     How SQL divides integers numbers            Easy           2

    -----------------

    Can you calculate 100! in your head?          Difficult        1

    etc.        


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, March 13, 2013 10:00 PM