| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Random Numbers in PL/SQL
A copy of this was sent to "Paulo Jorge Leitão" <paulojl_at_esoterica.pt>
(if that email address didn't require changing)
On Tue, 27 Apr 1999 17:39:09 +0100, you wrote:
>HI!
>
>in Oracle 8i you can use the rownum in your select
>
>select * from table_1 where rownum in (23,67,89,...,)
>
No you can't, rownum's behaviour has not changed in 8i:
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> select * from emp where rownum in ( 2, 3, 4 );
no rows selected
SQL> select count(*) from emp;
COUNT(*)
14
SQL> In 8i, you can sample the rows either by block sampling (eg: sample X% of the blocks) or by row (sample X% of the rows). For example, the following samples 5% of the rows in the emp table (5% = 7/10 of a row so sometimes we get 1 or 2, sometimes none).
SQL> select empno, ename from emp sample(5);
EMPNO ENAME
---------- ----------
7521 WARD SQL> / no rows selected
SQL> /
EMPNO ENAME
---------- ----------
7839 KING SQL> / no rows selected
SQL> /
EMPNO ENAME
---------- ----------
7844 TURNER
SQL> /
EMPNO ENAME
---------- ----------
7654 MARTIN
7900 JAMES
SQL> /
EMPNO ENAME
---------- ----------
7902 FORD
SQL>
>you only need to generate random numbers of the rows you want
>
>hope it helps
>
>
>
><youngsod_at_my-dejanews.com> wrote in message
>news:7g3t28$8lr$1_at_nnrp1.dejanews.com...
>> Sorry if this has been asked before, but I need some help.
>>
>> I have a table with around 27000 records at present and growing.
>>
>> The primary key is a varchar2(7) ID 1234567 , not created sequentially.
>>
>> I need to be able to get a random sample of 100 records from the table.
>>
>> Any ideas ?
>>
>> Douglas
>>
>> -----------== Posted via Deja News, The Discussion Network ==----------
>> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |