Random record from sql call

Results 1 to 2 of 2

Thread: Random record from sql call

  1. #1
    Join Date
    Dec 1969

    Default Random record from sql call

    I can create a random number function, but can someone help me with this? I have a recordset of X number of records, I need to pull the record whose cursor position IN THE RECORDSET matches the random number. Given that the random number I generate is between 1 and X where X is the total number of records in the recordset.

  2. #2
    Join Date
    Dec 1969

    Default RE: Random record from sql call

    There are several ways to do this, but if the number of records in the recordset isn&#039t *TOO* large, and especially if you will "pull" several records from the recordset, then the best way is probably to simply do<BR>&nbsp;&nbsp;&nbsp;&nbsp;rowsArray = RS.GetRows<BR>which converts the entire recordset to an array, where you access the elements of the array via<BR>&nbsp;&nbsp;&nbsp;&nbsp;someElement = rowsArray( fieldNumber, rowNumber )<BR><BR>Note that the array lower bound is 0, so to pick a random row number in the array you will do<BR>&nbsp;&nbsp;&nbsp;&nbsp;randomRow = Int( (1 + UBound(rowsArray)) * Rnd )<BR><BR>Now, *IF* the record set is quite large (not just number of rows...total bytes of memory needed to hold it...say more than a few K bytes total), and *IF* you will only select one or two random records from the record set, then you would likely be better off controlling the RecordSet object, directly.<BR><BR>You might do something like this:<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;Set RS = Server.CreateObject("ADODB.RecordSet")<BR>&nbsp;&n bsp;&nbsp;&nbsp;RS.CacheSize = 1 &#039 only one record at a time<BR>&nbsp;&nbsp;&nbsp;&nbsp;RS.Open "Select ...", someConnection, adOpenStatic, adLockReadOnly<BR>&nbsp;&nbsp;&nbsp;&nbsp;maxRecNu m = RS.RecordCount<BR>&nbsp;&nbsp;&nbsp;&nbsp;randomRe cNum = 1 + Int( maxRecNum * Rnd ) &#039 starts at 1!!<BR>&nbsp;&nbsp;&nbsp;&nbsp;RS.Move randomRecNum<BR>&nbsp;&nbsp;&nbsp;&nbsp;...<BR><BR >By setting the CacheSize to only 1 record, we don&#039t waste the transfer of unneeded records from the DB server to ASP.<BR><BR>Be careful how you construct the random number. Note the differences in the two I constructed, above.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts