FULL DETAILS: let me explain more clear. this is a table including about 100 question. every question has a BooKRange property that shows from which part of the book, this question hast fetched with values 1,2,3,4. and there is another property called Level that shows level of the difficulty of the question with values 1,2,3,4,5. now i need to randomly select 20 question that have to include all four Book Ranges and all five levels with a normal distribution.
please consider that i need to select distinct rows.
thank you very much.
edit: added the table
CREATE TABLE [dbo].[Question] (
[QuesID] INT IDENTITY (1, 1) NOT NULL,
[BookRange] NVARCHAR (50) NULL,
[Level] NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([QuesID] ASC)
);
You can do this query (assuming a uniform distribution) without doing a union. You just need to specify the ordering correctly.
If you want to select 5 questions from each of the levels, then you can do so by assigning a sequential number to the questions in each level. If these are assigned randomly, then you should meet the requirement of randomness for the levels:
with q as (
select q.*,
row_number() over (partition by [range] order by newid()) as seqnum
from Question q
)
select *
from q
where seqnum <= 5;
If you want to ensure that these is exactly one question for each level and range, but want the questions random, then do:
with q as (
select q.*,
row_number() over (partition by [range], [level] order by newid()) as seqnum
from Question q
)
select *
from q
where seqnum = 1;
By the way, range and level are reserved words in SQL Server. In general, it is good practice to avoid using reserved words for the names of things like tables, columns, stored procedures, and so on.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments