MSSQL random select

Hi,
Lets say I have a database table with 100 rows as game questions, how to select randomly 10 questions among the 100 and this random set changes every time I play again?
tks
PM150Asked:
Who is Participating?
 
jonnidipConnect With a Mentor Commented:
Please note that on a  large table (which may not be your case) you may encounter very poor performance by using "order by".
Note that setting the "order by" clause on your query the SQL needs to sort all the rows in tempdb. You can see this by displaying the estimated execution plan (CTRL+L in SSMS).

A solution can be to use the "tablesample" clause after the table name.
This will limit the rows returned, by a number of rows or by percentage.
After that you can still use the "order by newid()", as suggested before:
SELECT TOP 10 * FROM QUESTIONS TABLESAMPLE(1000 ROWS)
ORDER BY NEWID()

Open in new window


Please check again with the execution plan (check eventually the 2 queries one next to the other)...


Regards.
0
 
EvilPostItCommented:
SELECT TOP 10 * FROM QUESTIONS
ORDER BY NEWID()

Open in new window

0
 
EvilPostItConnect With a Mentor Commented:
Just did a quick test on my laptop. 1 million rows table had a structure of id field and question which was a char(200). The query completed in less that 1 second.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jonnidipCommented:
@EvilPostIt: I don't want to throw your solution. The poster said that the table had only 100 records... I just wanted to share my opinion over your (correct) solution.
In fact, I have tested your (correct and known as a standard) query on a table with over than 100 million rows and the time difference between the 2  queries (yours and the one I posted, with the addition of tablesample) is very high!

@PM150: The first solution is for sure correct. Just please remember that "size does matter"...
0
 
EvilPostItCommented:
The only problem with TABLESAMPLE is the fact that it returns a non-random set of results. So if you ask for 1000 it will choose a number rows in sequence and then a random 10 from that sequence.

The "size matters" comment made me laugh though :)
0
 
PM150Author Commented:
Tablesample is not really random indeed when want a new andom set while playing again. What about using cast(rand()*100 as int) on the row id?
Tks
0
 
EvilPostItConnect With a Mentor Commented:
RAND() would work if you were after a single question as it only produces 1 random number for all rows in the select statement. If you did only want 1 question you could do the following.

SELECT * FROM QUESTIONS
WHERE ID=CAST(RAND()*(SELECT MAX(ID) FROM QUESTIONS) AS INT)

Open in new window


You want to select 10 questions so using the above supplied statement will work fine in your scenario,
0
 
EvilPostItCommented:
Ok then.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.