Hello - My objective is to get x number of random records. x is a user defined number. Sounds fairly easy.....I am probably over thinking it however would very much apriciate opinions and suggestions.

Situation - I have a table with 10,000 records which does have an auto incrimenting integer PK field. The user enters a number leats say 25 therefore I want to sho 25 random records.

- My preference would be to do it all in the SQL statement however I can not find a function for it. Nor have I even come close to getting it. Is there a way?

- Next I am thinking of getting all the records (just ID field) placing into array. Now somehow choose 25 random elements, place into a commer delimited string and using this string to execute something like:

sql = "SELECT ...... FROM .... WHERE ID IN (myComerDelimitedSting)"

Then I thought is there a better way and come to the conclusion I have probably over thought it. Have I? Anyone care to share thier wisdom and suggest how they would do it?

TYIA





Wind is your friend
Matt
www.elitemarquees.com.au

__________________
Wind is your friend
Matt

Ummm...you need to tell us what database.

With SQL Server, it's trivial:
    SELECT TOP 25 <field list> FROM <table> ORDER BY NEWID()

Might be more efficient ways, though.

Clearly, *IF* you have an autoincrement PK that does *NOT* have any gaps (e.g., from prior deletes), then your idea of picking N random integers in the range and doing
    WHERE PK IN ( ...list... )
will be the most efficient.

But typically, in tables this large you no longer have a sequence without gaps.

have had a couple of days off with the flu, hence the delay in replying...
You are correct there will be gaps because the records can be deleted.
Yes it is SQL server. So your reccomendation would be to:

> get top X number of records ordered by id
> detect and store in a table the upper most ID of the above records set
> next time they get this page extract the stored ID from the last search
> now get top X num of records where ID > the stored ID

I guess this would achieve the objective. If I wanted this page built I may want random records from, lets say different areas of the table. If done the way described above; the way these records are entered they would always be getting X number of chronologicaly ordered records.

Is there a way you are aware where I can get X unumber of random records from an array of all records? How do I exclude records previously shown (this has not been asked however forward thinking tells me they may want to know what records have been previously shown) I was thinking of storing the shown ID's in a table and including these in the Where part of of my statement EG WHERE ID not IN (my comer delimited string of previously shown ID's)

TYIA

Wind is your friend
Matt
www.elitemarquees.com.au

What I have come up with seems to work well. The only thing that faults it would be deleted records. This is not ging to cause an error, the user will just get a few less records than they asked for. The table doesnt seem to have may gaps so im not to concerned - would apriciate constructive criticism??

  if trim(request.form("howManyRecords")) <> "" then
     howManyRecords = trim(request.form("howManyRecords"))
  else
     howManyRecords = 25 'default load
  end if

  sql = "SELECT top 1 licenceID from licence order by licenceID desc;"
  set getTopId = conn.execute(sql) 'get highest ID

  sql = "SELECT top 1 licenceID from licence order by licenceID;"
  set getBotId = conn.execute(sql) 'get lowest ID

  'generate a comer delimited random number string between two given numbers
  'these two numbers are the highest and lowest ID's
  Dim randomNum,randomNumString
  for counter = 1 to howManyRecords
     RANDOMIZE
     randomNum= Int((getTopID(0) - getBotID(0) + 1) * Rnd + getBotID(0))
     randomNumString = randomNumString & randomNum & ","
  next
  'must strip the trailing comer from the end of the string
  sql = "SELECT ... FROM .... WHERE licence.licenceID IN (" & Left(randomNumString,Len(randomNumString)-1) & "); "
  set getInfo = conn.execute(sql)

Wind is your friend
Matt
www.elitemarquees.com.au

been on holiday, so didn't see you question.

Your solution is efficient, of course, so only flaw is that it might get too few records. Easy compromise answer: Get maybe twice as many random records but only display the requisite number (e.g., do SELECT TOP 50 when you need 25). Still possible to get too few records but much less likely.

Alternative: If the table isn't updated too often, then create an auxiliary table with a NEW separate IDENTITY field and in int field that is the PK of your main table:
    SELECT pk INTO newTblWithIdentity FROM table

Now you have sequential ID values in that table. So you do
    SELECT t.* FROM table as t, newTblWithIdentity as n
    WHERE t.pk = n.pk AND n.identityID IN (...list...)

Obviously a bad solution if your main table is updated frequently; fine solution if updates are (for example) nightly. Just recreate the newTbl after the updates are done.

Good day - I ended up not being happy with the possibility of fewer records. Here is the loop within the loop which ensures the correct number of records are retrieved and displayed. I thought to post is incase it helps someone else. It works well and is about th most effcient way I thought to code it.

<% dim recCount
     if not getInfo.eof then %>
      <tr>
       <td class="Header5">Lic #</tD>
       <td class="Header5">Licence Category</tD>
       <td class="Header5">Licence Type</td>
       <td class="Header5">Last Modified</td>
       <td class="Header5">Applicant Name</tD>
<% do until getInfo.eof %>
         <tr>
          <td><%= trim(getInfo(1)) %></a></tD>
          <td><%= trim(getInfo(3)) %></td>
          <td><%= trim(getInfo(4)) %></td>
          <td><%= auDate(getInfo(5)) %></td>
          <td><%= trim(getInfo(6)) & " " & trim(getInfo(7)) %></td>
         </tr>
<% getInfo.moveNext
         recCount = recCount + 1
      loop
      do until (cint(recCount) = cint(howManyRecords))
         RANDOMIZE
         randomNum2= Int((getTopID(0) - getBotID(0) + 1) * Rnd + getBotID(0))
         sql = "SELECT licence.licenceID,licence.licenceNumber,licenceTyp e.categoryID,category.categoryDesc,licenceType.lic enceTypeDesc,licence.dateTimeStamp,entity.name1,en tity.name2 FROM ((((licence LEFT OUTER JOIN licenceType ON licence.licenceTypeID = licenceType.licenceTypeID)LEFT OUTER JOIN entity ON licence.holderID = entity.entityID)LEFT OUTER JOIN category ON licenceType.categoryID = category.categoryID)LEFT OUTER JOIN vwLicCurrentStatus ON licence.licenceID = vwLicCurrentStatus.licenceID) WHERE ((licence.licenceID = " & randomNum2 & ") AND licence.licenceID NOT IN (" & Left(randomNumString,Len(randomNumString)-1) & ") AND (vwLicCurrentStatus.isLicensed=1)); "
         set getInfo = conn.execute(sql)
         if not getInfo.eof then %>
            <tr>
             <td><%= trim(getInfo(1)) %></a></tD>
             <td><%= trim(getInfo(3)) %></td>
             <td><%= trim(getInfo(4)) %></td>
             <td><%= auDate(getInfo(5)) %></td>
             <td><%= trim(getInfo(6)) & " " & trim(getInfo(7)) %></td>
            </tr>
<% recCount = recCount + 1
         end if
      loop
    end if %>

Wind is your friend
Matt
www.elitemarquees.com.au

BTW for others who may need to achieve the same objective:

> the code above of course follows the code posted earlier.

> IMO the first method is fairly efficient. The second part which unfortunatly has an SQL statement within the loop does make it less efficiant however it only runs that SQL the numbers of times we are short of our required recordset. Probably very few, its only there in case we are short.

Wind is your friend
Matt
www.elitemarquees.com.au