Random Row with mySQL

Results 1 to 2 of 2

Thread: Random Row with mySQL

  1. #1
    Join Date
    Dec 1969

    Default Random Row with mySQL

    Hiya&#039;s,<BR><BR>I&#039;m having this problem, I have to select a random recordset from a table in a mySQL database. Now I&#039;ve done this a hundred times against a SQLserver database with-out any problems but when I attempt it again the mySQL it breaks. Here is how Ive been doing it.<BR><BR>Set objRSa = Server.CreateObject("ADODB.Recordset") <BR><BR>objRSa.Open "SELECT * FROM tblmain WHERE tblmain.ID="&IDvalue&" ;", objConna, adOpenStatic, adLockReadOnly<BR><BR><BR> rndMax = CInt(objRSa.RecordCount) <BR><BR> objRSa.MoveFirst <BR><BR> Randomize Timer <BR> <BR> rndNumber = Int(RND * rndMax) <BR><BR> objRSa.Move rndNumber <BR><BR><BR> blah= objRSa("username") <BR><BR><BR>objRSa.close <BR>Set objRSa = nothing <BR><BR>Now it brings up two errors:<BR>Rowset doesnot support fetching backwards <BR>Either EOF or BOF is true, no result returned......<BR><BR>Any help or ideas on how i can select a random recordset from mySQl would be great,<BR>Thanks,<BR>Para<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Easy with MySQL!

    Easier than with most any other DB!<BR><BR>MySQL has an ability to specify the record number of both the first and last records of the range to be returned!<BR><BR>So you can do something like:<BR><BR>SELECT * FROM table ORDER BY whatever RANGE 17,17<BR><BR>I forget what the keyword to do that is (I don&#039;t think it&#039;s actually RANGE), but I do recall that it has to be put at the *end* of the SQL statement, instead of at the beginning (unlike TOP with Access and SQL Server, for example).<BR><BR>If you can&#039;t figure it out, email me at junco.junction@verizon.net<BR><BR>Now, this will only get you one record! (Well...or a group, starting at a random record number?) If you want to get many random records, then you might still want to use one of the techniques that I discuss in the ASPFAQs.<BR><BR>Anyway, doing this with the SQL statement, instead of using that hrribly slow performing RS.MOVE, is easy and efficient.<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