?
Solved

Selecting n rows from a databse randomly

Posted on 2003-03-04
14
Medium Priority
?
2,542 Views
Last Modified: 2011-09-20
hi all
   well the problem is i want to select n random rows from my database using select statement .The actual problem is i have a databse which has some 1000 questions i need to select 10 questions at random from these 1000 questions and each time i use this select statement the rows fetched should be different can any one help.

Thanks
Itssreekant
0
Comment
Question by:itssreekant
[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
  • 3
  • 2
  • +2
14 Comments
 
LVL 6

Expert Comment

by:M-Ali
ID: 8069772
Try this:

SELECT * FROM table_name
WHERE primary_key IN
 (SELECT primary_key FROM
  (SELECT primary_key, SYS.DBMS_RANDOM.RANDOM FROM table_name ORDER BY 2)
  WHERE rownum <= 10 );


Replace table_name, and primary_key above and test it.


Ali
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 8070107
SELECT * FROM table_name SAMPLE(1)

Jeff
0
 

Expert Comment

by:philcartmell
ID: 8070134
A much easier way to do random ordering is:

select *
(
 select email
 from users
 order by dbms_random.value
)
where rownum <= 10

Essentially keep the SELECT * on first line and change the SELECT in the brackets - always leaving the ORDER BY dbms_random.value

Providing you've got the dbms_random package installed it will work, if you havent use the following code:

To check to see if its installed run the following (as any user):

select text from all_source
where name = 'DBMS_RANDOM'
and type = 'PACKAGE'
order by line

If it comes back with no rows selected it means you need to run the code below.

using svrmgrl (or sqlplus if you are 8.1.7+) execute the following packages:

@utlraw
@prvtrawb.plb
@dbmsoctk
@prvtoctk.plb
@dbmsrand

Cheers

Phil
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Expert Comment

by:philcartmell
ID: 8070142
Oops - you'll need to put a FROM after the inital SELECT *

Sorry about that - too early in the morning for me! :)

Cheers

Phil
0
 

Expert Comment

by:philcartmell
ID: 8070165
As per jeffs comments - that code will not work.

It would need to be:

 select email
 from users
 SAMPLE (99.999)
 WHERE rownum<=10
 
Sample provides a 'sample' (funnily enough) of the selected data - the argument for sample what percentage of data (returned from the select) you would like to sample. To complete Jeffs code you would first of need to limit it to 10 rows and then tell it you want to sample 99.999% of these rows. The range is .000001 to (but not including) 100, hence the 99.999% which is a pretty messy way to do it - Stick with dbms_random.value - a lot cleaner.

Cheers

Phil
0
 

Expert Comment

by:JohnMason
ID: 8072394
I would use Phils initial suggestion which gives a different random sample each time its run.

Using SAMPLE always returns the same 10 rows.

Johnno
0
 

Accepted Solution

by:
philcartmell earned 300 total points
ID: 8072427
Hmm - Unless I am still half asleep I have never known SAMPLE to produce the same 10 rows each time - unless you only have 10 rows in your result and have sample set to 99.99999999%

Are you sure it does this??

Phil
0
 

Expert Comment

by:JohnMason
ID: 8072490
Phil

The code I ran was :

SELECT table_name FROM all_tables SAMPLE (99.99999999) WHERE rownum<=10


There are 82 tables in all_tables. Each execution of the statement returns the same 10 rows.


The following SQL returns 10 diffrent (random) rows on each execution.

SELECT * FROM
(
SELECT table_name
FROM all_tables
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 10


Johnno
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 8076277
philcartmell:

You are right, SAMPLE does not guarantee an exact number of rows.

However, when you provide numbers >~ 90 you obviate the need for SAMPLE. JohnMason gets the same rows each time because SAMPLE is returning ALL the rows in the table (99.99999999% of 82 is 82!) - only then is "where rownum <= 10" being applied.

I agree that my suggestion is not the best alternative for the problem at hand.

itssreekant:

I think you will need to create another table that stores which questions have been given to which people, so they will not be asked the same question twice. Use philcartmell's code and apply a where clause that excludes questions that have already been given.

Jeff
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9933304
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately one week.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help.jsp#hs5

patelgokul
EE Cleanup Volunteer
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 9939645
It seems to me that philcartmell's first comment is most likely to lead to a solution.
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9940032
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept : philcartmell

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0
 

Expert Comment

by:philcartmell
ID: 9940527
sounds good to me
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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