Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2554
  • Last Modified:

Selecting n rows from a databse randomly

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
itssreekant
Asked:
itssreekant
  • 5
  • 3
  • 2
  • +2
1 Solution
 
M-AliCommented:
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
 
jpkempCommented:
SELECT * FROM table_name SAMPLE(1)

Jeff
0
 
philcartmellCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
philcartmellCommented:
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
 
philcartmellCommented:
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
 
JohnMasonCommented:
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
 
philcartmellCommented:
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
 
JohnMasonCommented:
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
 
jpkempCommented:
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
 
patelgokulCommented:
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
 
jpkempCommented:
It seems to me that philcartmell's first comment is most likely to lead to a solution.
0
 
patelgokulCommented:
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
 
philcartmellCommented:
sounds good to me
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now