CODEWITHSUNDEEP
c#sqlsql-server-2008
ssg
ssg

Reputation: 121

How to select 20 random questions from each set in sql?

I am working on online question application.

I am Fetching Records from a database.

I have SQL database holding 1000 question in 10 set. I mean each set containing 100 questions. How can I take 20 random questions from each set? I mean how can I select 2 (as per request) random question from each set?

Upvotes: 8

Views: 576

Answers (3)

valex
valex

Reputation: 24144

If you need to get 20 random questions from each group here is an SQLFiddle example. SetNum here is a set ID

select * from 
(
select t.*, 
ROW_NUMBER() 
over (partition by setNum order by NewId()) rNum from t
) t2 where rNum<=20

Upvotes: 2

4b0
4b0

Reputation: 22323

Try:

SELECT TOP 20 * FROM [YourTable] ORDER By NEWID()

More About NEWID().

Upvotes: 3

Adeel Ahmed
Adeel Ahmed

Reputation: 1601

Try this if you want 2 random questions from each SET...

SELECT TOP 2 * FROM (SELECT * FROM [YourTable] WHERE SET_ID = 1) ORDER By NEWID()
UNION
SELECT TOP 2 * FROM (SELECT * FROM [YourTable] WHERE SET_ID = 2) ORDER By NEWID()
UNION
.
.
.
.
UNION
SELECT TOP 2 * FROM (SELECT * FROM [YourTable] WHERE SET_ID = 10) ORDER By NEWID()

Upvotes: 1

Related Questions