Wednesday, January 11, 2006

random number solution from tomkyte

more on unique no. January 07, 2006
Reviewer: Nikunj from india

Followup:

that is technically impossible, since Oracle numbers are 38 digits long - that
is not an infinite set of numbers.

what are you really trying to do here?

I am trying to do as below.
My user want to enter starting no., ending no. and list of random no. wants
between starting no. and ending no.

i.e. starting no. 200 and ending no. 400 he wants 50 random no. from list.

so i had created loop which will select random no. between the range and base on
user input on how much random no. he wants.

I had try but i am getting non unique nos.


Followup:

so, really what you want to do is

a) generate the set of all integers between "lo" and "hi"
b) get a random selection from that

no problem when stated like that - when stated the way you did the first time -
big problem!

Huge difference between stating what the goal is (then people can give
solutions) versus offering a partial solution and saying "finish it" :)



ops$tkyte@ORA10GR2> variable lo number
ops$tkyte@ORA10GR2> variable hi number
ops$tkyte@ORA10GR2> variable num_to_get number
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec :lo := 200

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec :hi := 400

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec :num_to_get := 5;

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> with data
2 as
3 (select level+:lo-1 l
4 from dual
5 connect by level <= (:hi-:lo+1)
6 )
7 select *
8 from (select * from data order by dbms_random.random)
9 where rownum <= :num_to_get;

L
----------
221
297
367
305
333

ops$tkyte@ORA10GR2> /

L
----------
287
344
332
315
277

ops$tkyte@ORA10GR2> /

L
----------
213
278
209
357
341

ops$tkyte@ORA10GR2> /

L
----------
347
397
249
295
354


That is but one approach.

No comments: