?
Solved

MSSQL  random select

Posted on 2013-07-01
8
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39290393
SELECT TOP 10 * FROM QUESTIONS
ORDER BY NEWID()

Open in new window

0
 
LVL 13

Accepted Solution

by:
jonnidip earned 525 total points
ID: 39292985
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 225 total points
ID: 39293413
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 13

Expert Comment

by:jonnidip
ID: 39293449
@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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39293641
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
ID: 39293859
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 225 total points
ID: 39295874
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
ID: 39299310
Ok then.....
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

800 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