Randomly Selectin from a Table

Results 1 to 2 of 2

Thread: Randomly Selectin from a Table

  1. #1
    Join Date
    Dec 1969

    Default Randomly Selectin from a Table

    How would one randomly select a record from a table if all the IDs are not in the same order?<BR>I.E.<BR>I have a table such as:<BR>ID&#124Name&#124Age<BR><BR>And I want to select a random record each time, but the ids may not be in sequential order (there may not be a 2 for example)... Thanxs....<BR><BR>Scott Phillips

  2. #2
    Mike Shaffer Guest

    Default RE: Randomly Selectin from a Table

    Hi Scott,<BR><BR>(I don&#039t know if you&#039re the same person (ScottB) that posted the message above, but here is a copy of the reply I sent to him:)<BR><BR>Here&#039s a pretty simple way to do it. It works on the principle of setting your pagesize to &#039 1&#039, thereby rendering your database (in principle) similar to a random access binary file. For example, to get the 25th record in the database, you simply set your &#039absolutepage&#039 to 25! So... following that logic, if you generate random numbers between 1 and the total number of records in the database, you will be able to rip out random records!<BR><BR>Here&#039s an example I used to pull random photos from a database to display to people when they hit a site:<BR>--------------------------<BR>&#060;%<BR>Set Conn = Server.CreateObject("ADODB.Connection")<BR>Conn.Op en("photos")<BR>&#039<BR>Set rs1 = server.CreateObject("ADODB.Recordset")<BR>rs1.Curs orLocation = 3 &#039adUseClient<BR>rs1.CacheSize = 1<BR>&#039<BR>rs1.Open "select * from Photos", conn<BR>rs1.PageSize = 1<BR>lngtotalpages = rs1.PageCount<BR>&#039<BR>randomize<BR>&#039<BR>&# 039 Now we&#039re going to get 4 random records<BR>for intX = 1 to 4<BR> lngRandomRec = lngTotalPages + 1<BR> do<BR> lngRandomrec = int( rnd * lngtotalpages ) + 1<BR> loop while lngRandomRec &#062; lngTotalPages<BR> &#039<BR> rs1.AbsolutePage = lngRandomRec<BR> &#039<BR> &#039 do whatever processing we need to do with the record in rs1, e.g. <BR> response.write lngRandomRec & " " & rs1(0) & " - " & rs1(2) & "<BR>"<BR> &#039<BR>next<BR>&#039<BR>&#039 Don&#039t forget to close everything and set it to &#039nothing&#039<BR>&#039<BR>rs1.close<BR>set rs1 = nothing<BR>conn.close<BR>set conn = nothing<BR>%&#062;<BR>------------------<BR><BR>I hope this helps! <BR><BR> - Mike<BR><BR><BR>

Posting Permissions

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