How to return rows in a random order
January 27th, 2006 By Eddie Awad
To make sure that rows are returned in no specific order, order by a random value. For example:
HR@XE> select * 2 from ( 3 select first_name 4 from employees 5 order by dbms_random.value() 6 ) 7 where rownum <= 3 8 / FIRST_NAME -------------------- Jack Randall Harrison HR@XE> / FIRST_NAME -------------------- Anthony Vance Michael
January 30th, 2006 at 7:50 am
Another way to return a sample set of data (procentual) is
select * from TABLE sample (10)
select * from TABLE sample (0.1)
Best regards,
Claudiu Ariton
January 31st, 2006 at 1:40 am
With Oracle 10g, you have also the ORA_HASH() function
SELECT * FROM sales WHERE ORA_HASH(cust_id, 9) = 1; PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD AMOUNT_SOLD ---------- ---------- --------- - ---------- ------------- ----------- 2510 6950 01-FEB-98 S 9999 2 78 9845 9700 04-FEB-98 C 9999 17 561 3445 33530 07-FEB-98 T 9999 2 170 . . . 740 22200 13-NOV-00 S 9999 4 156 9425 4750 29-NOV-00 I 9999 11 979 1675 46750 29-NOV-00 S 9999 19 1121 97 rows selected.ORA_HASH documentation:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/functions097.htm#SQLRF06313