# Select a column and assign it different percentages for its 2 rows

• ### 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

• 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 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 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

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