Select random sample of N rows from Oracle SQL query result

Question

I want to reduce the number of rows exported from a query result. I have had no luck adapting the accepted solution posted on this thread.

My query looks as follows:

select
    round((to_date('2019-12-31') - date_birth) / 365, 0) as age
from
    personal_info a
where
    exists
        (
        select person_id b from credit_info where credit_type = 'C' and a.person_id = b.person_id
        )
;

This query returns way more rows than I need, so I was wondering if there's a way to use sample() to select a fixed number of rows (not a percentage) from however many rows result from this query.

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-09-08 17:50:07Z

3

You can sample your data by ordering randomly and then fetching first N rows.

DBMS_RANDOM.RANDOM

select round((to_date('2019-12-31') - date_birth) / 365, 0) as age
From personal_info a 
where exists ( select person_id b from credit_info where credit_type = 'C' and a.person_id = b.person_id )
Order by DBMS_RANDOM.RANDOM
Fetch first 250 Rows

Edit: for oracle 11g and prior

Select * from (
    select round((to_date('2019-12-31') - date_birth) / 365, 0) as age
    From personal_info a 
    where exists ( select person_id b from credit_info where credit_type = 'C' and a.person_id = b.person_id )
    Order by DBMS_RANDOM.RANDOM
)
Where rownum< 250

edited Sep 8, 2020 at 17:50

answered Sep 8, 2020 at 17:34

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arturo Sbr Over a year ago

I think fetch frist N rows is not available on the version of Oracle that I am using (I think it's 10g). Is there a workaround to this?

Gordon Linoff · Accepted Answer · 2020-09-08 17:30:16Z

0

You can use fetch first to return a fixed number of rows. Just add:

fetch first 100 rows

to the end of your query.

If you want these sampled in some fashion, you need to explain what type of sampling you want.

answered Sep 8, 2020 at 17:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Arturo Sbr Over a year ago

I want to shuffle the results and fetch the first N rows. fetch does the job well, but I would have to re-index the row id, right? Is there no way to use sample() here?

Arturo Sbr · Accepted Answer · 2020-09-08 17:37:14Z

0

If you are using 12C, you can use the row limiting clause below

select
    round((to_date('2019-12-31') - date_birth) / 365, 0) as age
from
    personal_info a
where
    exists
        (
        select person_id b from credit_info where credit_type = 'C' and a.person_id = b.person_id
        ) 
FETCH NEXT 5 ROWS ONLY;

Instead of 5, you can use any number you want.

edited Sep 8, 2020 at 17:37

Arturo Sbr

6,41111 gold badges51 silver badges101 bronze badges

answered Sep 8, 2020 at 17:32

Somy

1,6441 gold badge7 silver badges14 bronze badges

1 Comment

Somy Over a year ago

Unfortunately, as far as I know there is no good way to do this in 10g except for the thread link you posted above. In order to adopt that you have to make sure to use order by in your inner query, otherwise it won’t work. But the issue is that the inner query would do a full table scan but in terms of output you would be able to limit the number of rows. Hope this helps!

Collectives™ on Stack Overflow

Select random sample of N rows from Oracle SQL query result

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related