Q

Sorting rows randomly

In PL/SQL I have created a table with random numbers generated from the DBMS_RANDOM routine. Now I need to sort

the rows and place them into another table, so that when the user extracts 'X' amount of records, they will get a true random sampling, and not just the ones at the top of the feed chain. What is the best way to sort, as I am putting records into another table?

I think best way to do it is to add a sequencing column S to the original table that will number the random numbers from 1 to N rows. When inserting to the new table, use X select statement that "chooses" random numbers between 1 AND N. Do X times:

INSERT INTO NEW_table ......
SELECT .... from OLD_TABLES
WHERE S = random_nbr (from 1 to N)

For More Information


This was first published in February 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close