How to retrieve random record from a table. Several methods:

1. Sort by random value and take the first:
SELECT * FROM (SELECT * FROM customer ORDER BY dbms_random.value) WHERE ROWNUM = 1;

2. Use the SAMPLE clause, which lets to select from a random sample of data from the table, rather than from the entire table:
SELECT * FROM customer SAMPLE (1) WHERE ROWNUM = 1;

3. Combining random value with SAMPLE clause:
SELECT *
FROM (SELECT * FROM customer SAMPLE(1) ORDER BY dbms_random.value)
WHERE ROWNUM = 1;

Last edited Jul 15, 2012 at 10:07 PM by alekseiv, version 1

Comments

No comments yet.