Solved

MSSQL  random select

Posted on 2013-07-01
8
422 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
ID: 39290393
SELECT TOP 10 * FROM QUESTIONS
ORDER BY NEWID()

Open in new window

0
 
LVL 13

Accepted Solution

by:
jonnidip earned 175 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 75 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 75 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

680 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