• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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
0
PM150
Asked:
PM150
  • 5
  • 2
3 Solutions
 
EvilPostItCommented:
SELECT TOP 10 * FROM QUESTIONS
ORDER BY NEWID()

Open in new window

0
 
jonnidipCommented:
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:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
EvilPostItCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now