SQL STATEMENT TO PICK 100 RANDOM RECORDS FROM MY DATABASE

[aviles22]

I HAVE A DATABASE CONTAING TEST QUESTIONS AND A THRU F ANSWERS. THE DATABASE CONTAINS ABOUT 1000 QUESTIONS.
I HAVE SET UP AN ARRAY X(100) WITH NUMBERS FROM 1 TO 1000
WITH NO DUPLICATES. HOW CAN I USE THE X(100) ARRAY IN AN SQL
STATEMENT. MY DATABASE HAS A QUESTIONID FIELD FROM 1 TO 1000
I NEED TO COMPARE THE ARRAY TO QUESTIONID FIELD TO GET A 100
RECORDSET.

 

[MalcolmW]

You could use the millisecond portion of the system time to select a record number, then repeat the process until you have 100 distinct records.
Not truly random, but maybe close enough.

 

[mark06]

If you are using Oracle, there is a DBMS_RANDOM utility that may be useful.

 

[aviles22]

I am using access2000 in visual basic 6. I added a field to my database called temporder as integer. Every time the user
asks for a new test I create a recordset containing every record. Then from the 1st to the last record I change the value of temporder to a random number from MalcolmW's advice
with no duplicate numbers. Then I create a new recordset containg the top 100 records order by temporder:

Set TempOrderDC.Recordset = DAODB36.OpenRecordset("SELECT TEMPORDER FROM TEST&quot
TempOrderDC.Recordset.MoveFirst
TempOrderDC.Recordset.MoveLast
TempOrderDC.Recordset.MoveFirst
TOTL = TempOrderDC.Recordset.RecordCount
Randomize Timer
Dim D(300)'Assuming there is no more than 300 records in database
For x = 1 To TOTL
REGETNUM:
D(x) = Int(TOTL * Rnd) + 1
For CHECK = 1 To x - 1
If D(x) = D(CHECK) Then GoTo REGETNUM
Next CHECK
Next x
For PUTTEMP = 1 To TOTL
TempOrderSet.Caption = D(PUTTEMP)
TempOrderDC.UpdateRecord
TempOrderDC.Recordset.MoveNext
Next PUTTEMP
Set ExamDC.Recordset = DAODB36.OpenRecordset("SELECT TOP 100 * FROM TEST ORDER BY TEMPORDER&quot

IF THIS IS A GOOD IDEA PLEASE LET ME KNOW
OR IF SOMEONE HAS A BETTER IDEA
LIKE "Select 100 RANDOM FROM TEST"