Randomly Select 1000 records from MYTABLE


Very simple question: “Select 1000 random accounts from MYTABLE table”

This is very common question in analytical environments, yet most of the solutions I’ve seen are far from simple and random.

You can use DBMS_RANDOM or DBMS_CRYPTO packages – both deliver similar results although some might say DBMS_CRYPTO is “more” random. I’d say use DMBS_CRYPTO if you have the grant, if not use DBMS_RANDOM (really, you won’t see that much difference).

Query below is very simple and has 3 stages (3 subqueries) – first generate random number for each record, then sort the data by random number, after that select first 1000 records.

 

SELECT ACCOUNT_ID
 FROM ( SELECT ACCOUNT_ID, 
               RANDOM_ORDER
          FROM ( SELECT ACCOUNT_ID,
                        DBMS_RANDOM.RANDOM RANDOM_ORDER
                        FROM MYTABLE )
      ORDER BY RANDOM_ORDER)
 WHERE ROWNUM < 1001       

 

It really cannot be easier than that.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: