How to get n Random records from a Table?

[starplus2010]

Hi Guys

I need to select N number of records from oracle Table.
Example:
10 records from a table.
I used "SAMPLE" Keyword.

"select * from Emp order by sample(1)"
It gives 1% of total records in a table.
It works fine and selcts randomly. But everytime no of records changes less or more.
I need only 10 records randomly.

Any Idea?
Please Help me.

 

[martincab]

The DBMS_RANDOM package provides a built-in random number generator.

Example:

select dbms_random.random from dual;


Martin Cabrera
Oracle DBA/Programmer

 

[shahzadzafar]

hi,
please try to use folloeing query.....

select * from
(select * from scott.emp order by dbms_random.random)
where rownum<10

Regards

Shahzad Zafar

 

[PAJam]

In the interest of preserving performance try this:
select * from emp
where rownum<11
order by dbms_random.random;

This is using Shahzad excellent suggestion but his query subqueries to the same table; which is scary if the table is large. I tested this on a table with an ID column and the return is different every time.

As a strange aside, I tried this for a colleague and it returns 0 to 4 records which seemed logically impossible!
select ROW_NUM from test_61
where ROW_NUM = abs(mod(dbms_random.random,86)+1)

The idea was to get one random record where rownum is between 1 and 86.

PAJam

 

[ChrisHunt]

Except that
[tt]
select * from emp
where rownum < 11
order by dbms_random.random;
[/tt]
doesn't work. It takes the first ten rows from the table and places them in random order. You have to use Shahzad's method to scan the whole table before you boil it down to the top ten.

You could, however, use the SAMPLE() operator to narrow the field down a little...
[tt]
select * from
(select * from scott.emp sample(1)
order by dbms_random.random)
where rownum<10
[/tt]
If the table contains less than, say, 150 rows you'll need to increase the size of the sample accordingly.

-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk