Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: