Selecting n rows from a databse randomly

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

SELECT * FROM table_name SAMPLE(1)

Jeff

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

Oops - you'll need to put a FROM after the inital SELECT *

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

Cheers

Phil

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

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

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

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

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:
https://www.experts-exchange.com/help.jsp#hs5

patelgokul
EE Cleanup Volunteer

It seems to me that philcartmell's first comment is most likely to lead to a solution.

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

sounds good to me