Q
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)
end

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


This was last published in June 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close