Finding random records.

[rpenridge]

Hi All,

I need to corrupt some data for demo purposes and need a way to replace the real customer number with a random customer number from my customer table. Sounds simple enough but...

What is the best way to return a random row from a table?

I need this to be quick! There is a lot of records in the table I am
dumping.

Thanks,
Rob

 

[bendaluz2]

Try this

I usually do this when I want to pick random records. I have written it to work on the sports database.

<PRE>
DEFINE VARIABLE i$seq AS INTEGER NO-UNDO.
DEFINE VARIABLE i$loop AS INTEGER NO-UNDO.
DEFINE VARIABLE i$rand AS INTEGER NO-UNDO.
DEFINE TEMP-TABLE tbl$random NO-UNDO
FIELD i$seq AS INTEGER
FIELD row$id AS ROWID
INDEX idx$main IS PRIMARY UNIQUE i$seq.

DEFINE BUFFER buf$random FOR tbl$random.

FOR EACH customer
NO-LOCK:
CREATE tbl$random.
ASSIGN i$seq = i$seq + 1
tbl$random.i$seq = i$seq
tbl$random.row$id = ROWID(customer).
END.

DO i$loop = i$seq TO 2 BY -1:
ASSIGN i$rand = RANDOM(1,i$loop).
FIND tbl$random
WHERE tbl$random.i$seq = i$rand
NO-ERROR.
FIND buf$random
WHERE buf$random.i$seq = i$loop
NO-ERROR.
IF AVAILABLE(tbl$random)
AND AVAILABLE(buf$random) THEN
ASSIGN tbl$random.i$seq = i$loop
buf$random.i$seq = i$rand.
ELSE
DISP "Couldn't find record".
END.

FOR EACH tbl$random
NO-LOCK,
FIRST customer
WHERE ROWID(customer) = tbl$random.row$id
NO-LOCK:
DISPLAY customer.cust-num.
END.
</PRE>

Remember to start up your progress session with the -rand 2 parameter, or you will always get your records in the same order!

Hope this helps.