Selecting 20 random distinct records

[ShotoCon]

Was wondering if anyone could give me any tips on this before i got started and got bogged down going down the wrong path!

I need to be able to select 20 distinct records from a database - but the selection also needs to be random. Its basically for a quiz so I cant have the same question sets repeating themselves.

I had thought to randomly select the questions id and store in an array and then just go through it for next question each time. Its just the randomly selecting part im not sure of!

Any thoughts or suggestions for me to try, pls just say, I'll give it a go.

Cheers

 

[BigRed1212]

Based on what I have learned hanging out here, rather than try to make an initial selection of 20 random records, I would take the approach of selecting all the possible questions as your recordset, bring that in, and then use the RND function in VB Script to get the randomly selected questions from that.

I would think as that as long as your database of questions is sufficiently large, you won't have a lot of repeats.

 

[gmmastros]

What database are you using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ShotoCon]

It'll just be running from an Access DB, the quiz doesn't need to do anything uber complicated, just hold the quiz questions and the contestants information.

 

[FesterSXS]

I use the following to select random records from a SQL Server database (also for a quiz). Not sure if it works with Access though:

SELECT TOP 20 field1, field2 FROM tbl_Questions ORDER BY NEWID()

Tony
---------------------------------------

http://www.SpiraxSarco.com/

 

[ShotoCon]

Hey

Unfortunately the SQL Server code doesnt work - i did however manage to find code on asp101.com which did the trick

Thanks for the help though ;oD

 

[FesterSXS]

That's great news - Care to post the code you found so that others searching with the same problem can make use of it?

Tony
---------------------------------------

http://www.SpiraxSarco.com/

 

[ShotoCon]

Yip Yip Yip

Here it is:

<%
'Connect to you database
'simplified for illustration

sql = "SELECT Id, Name, Image, url FROM tblGroup"

Set RS = conn.Execute(sql)

Dim arrData        'Array to Store Data
Dim arrSequencer   'Array to Hold Random Suequence
Dim iArrayLooper   'Integer For Looping
DIM iArraySize     'Size of Data Array

'First array index is field:
'0=id, 1=name, 2=image, 3=url
'Second array index = record number
arrData = RS.GetRows

'Moved to before the loop
RS.close
Set RS = Nothing
conn.Close
Set conn = Nothing

'determine the size of the data arrays 2nd (data) dimension
iArraySize = (UBound(arrData, 2) - LBound(arrData, 2)) + 1

'Get an aray of number 0 to array size randomly sequenced
arrSequencer = GetRandomizedSequencerArray(iArraySize)

For iArrayLooper = LBound(arrSequencer) TO UBound(arrSequencer)
   strId     = arrData(0, arrSequencer(iArrayLooper))
   strName   = arrData(1, arrSequencer(iArrayLooper))
   strImage  = arrData(2, arrSequencer(iArrayLooper))
   strUrl    = Trim (arrData(0, arrSequencer(iArrayLooper)))

   'Do whatever they were doing using the data
   'values we just retrieved from the RS.  I've
   'removed the code because it really has no
   'bearing on the article

Next

'### END OF RUN TIME CODE - FUNCTIONS FOLLOW ####

Function GetRandomizdSequencerArray(iArraySize)
   Dim arrTemp()
   Dim I
   Dim iLowerBound, iUpperBound
   Dim iRndNumber
   Dim iTemp

   'set array size
   ReDim arrTemp(iArraySize - 1)

   'Init Randomizer
   Randomize

   'Get bounds into local vars for speed
   iLowerBound = LBound(arrTemp)
   iUpperBound = UBound(arrTemp)

   'Insert Initial values
   For I = iLowerBound To iUpperBound
        arrTemp(I) = I
   Next

   'Loop through the array once, swapping each value
   'with another in a random location within the array
   For I = iLowerBound to iUpperBound
       'Generate random # in range
       iRndNumber = Int(Rnd * (iUpperBound - iLowerBound +1))
       
       'Swap Ith element with iRndNumberith element
       iTemp = arrTemp(I)
       arrTemp(I) = arrTemp(iRndNumber)
       arrTemp(iRndNumber) = iTemp
   Next  'I

   'Return the array
   GetRandomizedSequencerArray = arTemp
End Function
%>

NOTE: this is NOT my own personal code, once I find the URL i got it from again ill post there here too.

There ya go, its a bit inefficient in so far as it pulls the entire table information, then randomises. I tweaked it a little for my own purposes but it definately randomises the table everytime, tested it multiple times just to make sure.