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-08T17:50:07.480

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 08 '20 at 17:50

answered Sep 08 '20 at 17:34

Derviş Kayımbaşıoğlu

28,492
4
50
72

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? – Arturo Sbr Sep 08 '20 at 17:42

score 0 · Answer 2 · answered Sep 08 '20 at 17:30

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 08 '20 at 17:30

Gordon Linoff

1,242,037
58
646
786

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 Sep 08 '20 at 17:34

score 0 · Answer 3 · edited Sep 08 '20 at 17:37

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 08 '20 at 17:37

Arturo Sbr

5,567
4
38
76

answered Sep 08 '20 at 17:32

Somy

1,474
1
4
13

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! – Somy Sep 08 '20 at 17:58

Select random sample of N rows from Oracle SQL query result

3 Answers3