Problem solve Get help with specific problems with your technologies, process and projects.

Selecting random rows

Hi Rudy.
Is there a way to get a random (and not sequential) of X rows (with all data) in one query (and not X identical queries)?

Short answer: No.

Long answer: Yes, sort of, but you might not like it.

There is no way in standard SQL to select rows randomly. Some sort of programming is required.

Let's say you have a table with many rows and the primary key is an integer, like an autonumber or identity key or sequence number. If you can generate random keys from the range of primary keys in use, these may be used to select rows.

First, you need a "driver" table which will hold the generated keys --

create table Driver
  ( driverID autonumber
  , randno integer)

Next, find the range of primary keys in use --

select min(primaryID) as MinID
     , max(primaryID) as MaxID
  from yourTable

Now, generate some random keys using the min and max ID values as the range bounds (note: the following is pseudocode, not any specific programming language) and insert them into the driver table --

for 1 <= j <= 100
  r = random(minID, maxID)
  insert into Driver(randno) values (r)

Finally, run your select using the driver table of random keys --

select yourTable.*
  from Driver, yourTable
 where yourTable.ID =
     (select min(primaryID) from yourTable
       where primaryID >= Driver.randno)

Ugly, to say the least. And only pseudorandom. And not tested lately. Note the subquery is required in case there are gaps in the actual primary keys in yourTable.

Another way is to arbitrarily take a substring out of a string conversion of some convenient column in yourTable like a timestamp --

select TOP 100 yourtable.*
     , substring(cast(yourTimestamp as char(20))
                 from 14 to 18) as foo
  from yourTable
 order by foo desc

But that's even less random.

The best solution, for situations where you only need a small number of rows, such as in testing, is to have a sample table which contains not random rows of live data, but test cases -- in other words, a place where you can collect those rows which are of interest or which represent conditions you want to test for.

For More Information

Dig Deeper on Oracle and SQL