Solved

MSSQL  random select

Posted on 2013-07-01
8
410 Views
Last Modified: 2013-07-04
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
Comment
Question by:PM150
  • 5
  • 2
8 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
SELECT TOP 10 * FROM QUESTIONS
ORDER BY NEWID()

Open in new window

0
 
LVL 13

Accepted Solution

by:
jonnidip earned 175 total points
Comment Utility
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
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 75 total points
Comment Utility
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
 
LVL 13

Expert Comment

by:jonnidip
Comment Utility
@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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
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
 

Author Comment

by:PM150
Comment Utility
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
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 75 total points
Comment Utility
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
 
LVL 16

Expert Comment

by:EvilPostIt
Comment Utility
Ok then.....
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now