random records

[cruel]

I need to select a given number of random records. I have done this before, about 4 or 5 years ago, I forgot the function name? Anyone? Thanks

 

[taupirho]

For example select 6 random records (rownums)
from the all_objects table:

1 select r
2 from ( select r
3 from ( select rownum r
4 from all_objects )
5 order by dbms_random.value )
6* where rownum <= 6
SQL> /

R
----------
7286
17986
9972
9375
3323
10802

6 rows selected.

 

[jimirvine]

Excess sub query.
Just use:

select r
              from ( select rownum r
                      from all_objects
             order by dbms_random.value)
    where rownum <= 6;


         R
----------
     35809
     44910
     37879
     13011
      6428
     17288

6 rows selected.

SQL> /

         R
----------
     14144
     31970
     24823
      8509
     43047
     32094

6 rows selected.

 

[cruel]

thanks to both!

 

[cruel]

That is not the way I did it a few years ago. What I eventually want to achieve is to get, say 10 records within each group. For example, 10 random records from model A, another 10 random records from model B, etc. Isn't there a function like random or srand or something? Strange that I could not find it in documentation anywhere. Thanks

 

[taupirho]

Post some sample data please and the output you expect or would like.

 

[cruel]

Model QTY
A 10
A 5
...
A 20

B 10
B 5
...
B 20

...

I want to select randomly 2 records from those where model='A' and 2 records from those where model='B'. Thanks

 

[taupirho]

Well one obvious way would be to use unions, viz

select model,qty
              from ( select model,qty
                      from model where model = 'A'
             order by dbms_random.value)
    where rownum <= 2
union
select model,qty
              from ( select model,qty
                      from model where model = 'B'
             order by dbms_random.value)
    where rownum <= 2

 

[cruel]

thanks, but I forgot to mention the list of model is very long. I have plan B (loop) and plan C (combined with other programming language), but they are efficient enough, because the data is very large.

 

[taupirho]

OK, try this out:

select model,qty
from
(
select model,qty,row_number() over (partition by model order by model) r2
from
(
select model,qty
from model
order by model,dbms_random.value
)
) where r2 <= 2