Picking a random row

Picking a random row

Post by Tim Ha » Sat, 22 Nov 1997 04:00:00





>We've got a table with about 600,000 rows, and want to pick a random row
>from it.  The brute force method would be to create a new column and fill
>it with sequential numbers, than pick a random integer from 1 to the
>number of rows and select that.  There's a number of reasons we don't want
>to do this, mostly because it's a pain to retain the sequence as rows are
>added and deleted.  Any better ideas?

>--

It's not too difficult to maintain a sequence for inserts.  If you
don't want to resequence on deletes, you could program the random row
selector to loop until it found a sequence number that matches a valid
row.  Unless you're performing a *lot* of deletes, it's going to get a
valid hit the first time round most of the time.
-----------------------------------------------------
Tim Hall, Indus International (was TSW International)

http://www.tswi.com
http://www.indusgroup.com

Replace domain name with the domain from the first URL before replying via email!

 
 
 

Picking a random row

Post by Dave S » Sat, 22 Nov 1997 04:00:00


Agreed it might be inefficient, but if we don't want to add new table
elements, we might be limited ? No?

The other items I could think of might (repeat might) be the use of the
rowid somehow?
Or if there is some item that might be unique and it's in some sort of
range, you might want to try and write some code that would generate a
random number between (x) and (y) and then do a 'select count(*) from table
where <unique_key> = <random number>'.

Put this in some loop and if the item is not found, generate a new random
number?

Sorry, I'm out of idea's,  if you do manage to resolve this issue, please be
sure to let me know.  I'm not certain if anyone else out in *space is
interested, but I'd like to know.

Regards,   Dave



>> Then you could loop through this kind of select statement until you have
>> selected enough rows?

>> Would this not suffice?

>Oh, god, no.  The table has 600,000 rows.  I would work, sure, but it
>would be hideously inefficient.

>--

>New York University School of Medicine
>550 First Avenue, New York, NY  10016


 
 
 

Picking a random row

Post by Roy Smi » Sat, 22 Nov 1997 04:00:00



> Then you could loop through this kind of select statement until you have
> selected enough rows?

> Would this not suffice?

Oh, god, no.  The table has 600,000 rows.  I would work, sure, but it
would be hideously inefficient.

--

New York University School of Medicine
550 First Avenue, New York, NY  10016