CODEWITHSUNDEEP
sqldatabaseoracle-databaserandom
learner stuff
learner stuff

Reputation: 21

How to retrieve random records from Oracle with dbms_random

I am a beginner in sql and I was wondering if you could help me figure out how to retrieve random records from Oracle. I have the following query:

SELECT p.* from data p where p.id in 
(SELECT DISTINCT t.id   FROM DATA t, comment c, PTV ptv   
WHERE t.code not in ('x','y','Z') 
and t.id = c.id(+)   and ptv.code = t.code   and ptv.code = t.code   
and ptv.version = t.version  AND t.TASK_CODE != 'DRAFT')

How can i select X number of random records from the results returned by the above query? Any suggestions will be appreciated.

Upvotes: 0

Views: 357

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

The "easy" way to get a random number is to use dbms_random.value or dbms_random.random() and rownum:

with t as (
      <your query here>
     )
select x.*
from (select t.*
      from t
      order by dbms_random.value
     ) x
where rownum < 10;

I suspect there are better ways to write your query. If you want suggestions on that front, write another question, include sample data, desired results, and describe what you want the query to do.

Upvotes: 3

ibre5041
ibre5041

Reputation: 5298

Use SAMPLE clause:

select * from data t SAMPLE(1) left outer join comment c on (t.id = c.id)

Upvotes: 2

Related Questions