Menu
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?
382
To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).
The SQL SELECT RANDOM() function returns the random row.
Whenever we need to sort a given SQL query result set, we have to use the ORDER BY clause. However, to randomize the returned rows, we need the ORDER BY clause to use a function or database object that returns a random value for each row contained in the SQL result set.
Try:
SELECT TOP 20 * FROM [YourTable] ORDER By NEWID()
More About NEWID().
185
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
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
