Randomizing database records

[Bomber2006]

Hello

Can anyone tell me which is the best method (or the code) for randomizing database records? The selected record, after randomization, is to be placed in a text box. I am using VB6 and Access 97.

Thanks

[Hack]

Is there a field (hopefully numeric field) that could be used as a key in randomizing?

[Bomber2006]

Yes the Primary key id is numeric

Thanks

[LaVolpe]

One possible solution: If you want to return complete recordset or a subset, query the DB returning the record ids. Size a Long array of the recordcount and populate the array with the record ids. Then pass the array to a routine similar to following. The array will be randomized when it returns. The array are your randomized record ids, so to return one from the db, either query on the value in the array or if an existing recordset was maintained, use .Find to locate the random record

Private Function ShuffleExistingArray(inArray() As Long) As Boolean

    ' inArray must be a populated 1D array. Any LBound can be used
    
    Dim X As Long, rndNr As Long
    Dim tmpArray() As Long
    Dim lOffset As Long, nrItems As Long
    
    On Error GoTo EH
    tmpArray = inArray                              ' copy array structure
    lOffset = LBound(tmpArray)                      ' get its LBound
    nrItems = Abs(UBound(tmpArray) - lOffset)       ' calc nr of array items in ref to 0-LBound
    For X = nrItems To 0 Step -1                    ' begin randomly filling output array
        rndNr = Int(Rnd * (X + 1)) + lOffset        ' get rnd Index from temp array
        inArray(X + lOffset) = tmpArray(rndNr)      ' add to output array
        tmpArray(rndNr) = tmpArray(X + lOffset)     ' swap last array Index with selected array Index
    Next
    ShuffleExistingArray = True                     ' done
        
EH:
    If Err Then
        MsgBox Err.Description, vbOKOnly
        Err.Clear
        inArray = tmpArray
    End If
End Function

[leinad31]

One possible solution: If you want to return complete recordset or a subset, query the DB returning the record ids. Size a Long array of the recordcount and populate the array with the record ids. Then pass the array to a routine similar to following. The array will be randomized when it returns. The array are your randomized record ids, so to return one from the db, either query on the value in the array or if an existing recordset was maintained, use .Find to locate the random record

Private Function ShuffleExistingArray(inArray() As Long) As Boolean

    ' inArray must be a populated 1D array. Any LBound can be used
    
    Dim X As Long, rndNr As Long
    Dim tmpArray() As Long
    Dim lOffset As Long, nrItems As Long
    
    On Error GoTo EH
    tmpArray = inArray                              ' copy array structure
    lOffset = LBound(tmpArray)                      ' get its LBound
    nrItems = Abs(UBound(tmpArray) - lOffset)       ' calc nr of array items in ref to 0-LBound
    For X = nrItems To 0 Step -1                    ' begin randomly filling output array
        rndNr = Int(Rnd * (X + 1)) + lOffset        ' get rnd Index from temp array
        inArray(X + lOffset) = tmpArray(rndNr)      ' add to output array
        tmpArray(rndNr) = tmpArray(X + lOffset)     ' swap last array Index with selected array Index
    Next
    ShuffleExistingArray = True                     ' done
        
EH:
    If Err Then
        MsgBox Err.Description, vbOKOnly
        Err.Clear
        inArray = tmpArray
    End If
End Function

If an error occurs you'd return an array with duplicated items... just select random array elements and swap them within same array.

[LaVolpe]

Thanx for the tip. Simply removing the line: inArray = tmpArray from the EH: section would suffice. The only error would be lack of memory to build the tmpArray, which if it happens would occur before loop starts and the inArray in the EH section should not be allowed to be modified then.

[leinad31]

Please refer to post #24 by Logophobic to minimize bias in randomization http://www.vbforums.com/showthread.p...andomize+array

[anhn]

Firstly, you don't need to use a second array to suffle a given array.
That will be inefficient with a large array. To suffle an array I will do this:

Function SuffleArray(ByRef inArray As Variant) As Boolean
   Dim i As Long
   Dim j As Long
   Dim iMin As Long
   Dim iMax As Long
   Dim vTemp As Variant
   
   If IsArray(inArray) Then
      iMin = LBound(inArray)
      iMax = UBound(inArray)
      Randomize
      For i = iMin To iMax
         j = Int((iMax - iMin + 1) * Rnd()) + iMin
         vTemp = inArray(i)
         inArray(i) = inArray(j)
         inArray(j) = vTemp
      Next
      SuffleArray = True
   End If
End Function

Secondly, no need to build a random array to get a random record from a table. This is my way:

1. Open a DAO or ADO recordset : rst)
2. Get RecordCount of rst : n -- use rst.MoveLast if required
3. To get a random record:

• Offset = Int(n * Rnd()) -- between 0 and n-1
• rst.MoveFirst
• rst.Move Offset
• Read record data

[LaVolpe]

anhn, I like your tweaks to the shuffle routine. The array method allows entire "list" to be shuffled without repeating. The second solution allows potential of repeating records. Depending on user's needs, they have choices.

[anhn]

Another method: Randomly sort the recordset.

Open a DAO or ADO recordset based on the below SQL:
SQL = "SELECT Table1.*, FROM Table1 ORDER BY Rnd()"

Now you can get data of one record at a time from the top to the bottom of the recordset that is already in a random order.

Edit: This method may not work because Rnd() has the same value for all records. Need to tweak some more.

Edit2: It may work if we pass RecordID to Rnd():
SQL = "SELECT Table1.*, FROM Table1 ORDER BY Rnd(Table1.RecordID)"

[leinad31]

Behaviour of Rnd() or similar database function is database dependent... it won't work with access cause same value is returned for all records while seeding introduces bias.

[anhn]

Behaviour of Rnd() ... while seeding introduces bias.

I think bias is not a big deal in this case. I just thought a way like that.
It's good if someone input some tweaks to make it works as simple as possible.

[leinad31]

In retrospect, he wasn't very clear of his requirements. Such as if repetitive selection is allowed, or single selection until all numbers are exhausted, or how many random samples he will be retrieving from the population, etc.

It would be best to wait for his response and refrain for the time being from lengthening this thread with more code/posts.

[Bomber2006]

Hello again.

Thank you for your replies.

Leinad31 asked for some further information regarding the purpose of the query. We want to be able, from time to time, to select between one and five of our customers for investigative purposes. It doesn't matter if the same customer is selected again at a later date.

[szlamany]

RAND() in MS SQL will never work in a SELECT statement - notice this

select rand(),* from ledtype_t

It returns

                       LedType LedTypeDesc      Hierarchy                      TDate
---------------------- ------- ---------------- ------------------------------ -----------------------
0.204834390892421      AP      Accounts Payable                                NULL
0.204834390892421      BB      Budget Book      D                              NULL
0.204834390892421      BP      Budget Priors    G                              NULL
0.204834390892421      BR      Revised Budget   F                              NULL
0.204834390892421      BT      Budget Transfer  F                              NULL
0.204834390892421      BU      Beginning Budget G                              NULL
0.204834390892421      GP      G/L Priors       H                              NULL
0.204834390892421      HC      Hand Checks      L                              NULL
0.204834390892421      IN      Invoices         L                              NULL
0.204834390892421      JE      Journal Entry    J                              NULL
0.204834390892421      LQ      Liquidation      L                              NULL
0.204834390892421      OC      Other Charges    M                              NULL
0.204834390892421      OR      Other Receipts   R                              NULL
0.204834390892421      PC      Payroll Charges  N                              NULL
0.204834390892421      PO      Purchase Order   V                              NULL
0.204834390892421      PP      Previous PO      V                              NULL
0.204834390892421      PX      Adjust PO        V                              NULL
0.204834390892421      RQ      Requisition      W                              NULL
0.204834390892421      TH      Threshold        D                              NULL
0.204834390892421      VD      Void Check/Inv                                  NULL

(20 row(s) affected)

The RAND() is evaluated once prior to the building of the working resultset. Each row gets the exact same value.

I've seen suggestions to place RAND() into a USER DEFINED FUNCTION with MS SQL - I think by doing that it gets evaluated row by row.

[anhn]

@szlamany, The same thing happens in Access as said in Post#10. It also won't work with a UDF.

Edit: This method may not work because Rnd() has the same value for all records. Need to tweak some more.

Edit2: It may work if we pass RecordID to Rnd():
SQL = "SELECT Table1.*, FROM Table1 ORDER BY Rnd(Table1.RecordID)"

[leinad31]

Hello again.

Thank you for your replies.

Leinad31 asked for some further information regarding the purpose of the query. We want to be able, from time to time, to select between one and five of our customers for investigative purposes. It doesn't matter if the same customer is selected again at a later date.

If there are no gaps in the sequence of your primary key then get "SELECT MIN(pk), MAX(pk) FROM yourTable" first, then generate 3-5 random numbers into a CSV using VB Rnd(), and finally "SELECT columns FROM yourTable WHERE pk IN (" & csv & ") " to get random records.

If there are gaps in the pk series (possibility that random number generated is a non-existent key) then you can simply retrieve more than you'll normally need, e.g. 10 records, and get your 3-5 records from that set.

[szlamany]

@anhn - apparently you cannot put RAND() into a UDF - it's non determistic so it's not valid.

But I guess you can put it in a VIEW and then reference the VIEW in the UDF - here's a link that discusses that

http://www.vbforums.com/showthread.p...14#post2148414

But of course this is all for MS SQL...

A concept you can use for any database would be:

1) Determine the number of rows in the table - SELECT COUNT(*) will do that
2) Use RND() from the client side - with the number of row value - to determine which record you want
3) Do a query something like this to get that one record

Select Top 1 * 
    From (Select Top {TheRandomRowFoundinStep2} *
                  From SomeTable Order by PriKey Desc) "XYZ"

The sub-query gets only the number of rows determined in step 2 to offer up the random row of choice.

Order by PRIKEY Desc will put the "last" record in that sequence first.

Select Top 1 will get just that record.

You can probably do something in the sub-query to get just the PRIKEY values and select WHERE PRIKEY=(...) that - lots of ways to handle this.