Monday, March 07, 2005

Retrieving random table rows from Oracle 8+ without stored procedures

This was a bear to find explained simply and by example. To retrieve six rows randomly from your table, WITHOUT using PL/SQL procedures (i.e. doable easily from a ColdFusion CFQUERY):
select courseid from 
(select courseid from mastercourse order by dbms_random.value) 
where rownum <=6;
(courseid is the primary key.)
The original example code was more ornate because it was demonstrating generating random numbers rather than random rows, through a bit of trickery involving counting all objects within your instance of Oracle.

dbms_random.value and rowid are Oracle-specific (the former as of Oracle 8); Google searches on "SQL random row"/variants show no generic SQL query which can do this. Please prove me wrong.