SPONSORS:






User Tag List

Page 1 of 2 12 LastLast
Results 1 to 10 of 12
  1. #1
    Senior Member
    Join Date
    Jul 1999
    Location
    Bellingham, WA USA
    Posts
    1,323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Random Fetch with SQL

    I'm looking for a way to pull random rows, say 10-12 random rows, from an Access database. Other than FetchNext and FetchPrev, how do I pull a random row from the database table?


    ------------------

  2. #2
    Senior Member
    Join Date
    Apr 2001
    Location
    San Diego,CA,USA
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    you can execute the sql statement to get the random rows and then do fetchnext in those random rows this should server your purpose, I hope

    statement will be something like this

    select
    ------------------

  3. #3
    Senior Member
    Join Date
    Jun 2000
    Location
    Hartford, CT, USA
    Posts
    163
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    That was an apt solution:
    Is there a way to call a C++ like Rand() function in the DB_FetchNext loop?

    ------------------
    Jaimini Bhatt
    jaiminita@hotmail.com
    jaiminita@yahoo.com
    Jaimini Bhatt

  4. #4
    Senior Member
    Join Date
    Jul 1999
    Location
    Bellingham, WA USA
    Posts
    1,323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    I've not had a chance to try this yet, but thank you for the rapid response!

    ------------------

  5. #5
    Senior Member
    Join Date
    Jun 1999
    Location
    Fremont, CA, USA
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    I do this all the time, but I just query to get all the data, place in a list or array, then, knowing the size of the list, I get the number of random values that I need, making sure I do not get duplicates (from 4Test).


    ------------------
    David Genrich
    david.genrich@e2open.com
    david@linuxpuppy.net

  6. #6
    Senior Member
    Join Date
    Dec 2001
    Location
    Bozeman, MT, USA
    Posts
    265
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    yeah, unless you have a numerical index its hard to get random DB data without copying it into silk.

    Automation_guru you have an interesting solution but how would u generate string strRand_string? By defining it there its kind of static and not random. What happens if this build you have an item 212 but tomorrow the DB changes and its no longer there.

    DJGray, if you have a numberical index you can do this. Query to get the Min from the index and the max from the index. Use these vals in your randint() and then query for the data. Check to make sure you are returned something(in case the index number has been removed and doesnt exist) and then append it to a list of string/record.

    If memory is not an issue and the query results are not too large, I would just grab all the data and put it into a list of string/record like davidgenrich said. Then you can get a randint( 1, listcount(lsMyList) and use the randint result like this:
    lsMyList[<randint() result> )

    ------------------
    Ryan McCullough
    Accelrys Inc.
    ryan@accelrys.com
    Ryan McCullough
    RightNow Tech. Inc.

  7. #7
    Senior Member
    Join Date
    Jul 1999
    Location
    Bellingham, WA USA
    Posts
    1,323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    Strangely enough, in seven years with this product, this is the first time I've ever had the need to do this sort of testing. So, I'm learning some new things and appreciate all your input!

    I have an access database with 196,507 records in it. I'm finding the the queries are painfully long and slow. In an effort to speed things up, I've read this into a record like this:

    So, if I understand this correctly, I have, held in memory, a list of records, and each record holds 11 string values.

    How, then, do I access that data? For example, lets say I want to print each sFirstName and sLastName where sZip is equal to 98226? What is the syntax to pull those values out of the records within the list of record?

    ------------------


    [This message has been edited by DJGray (edited 03-24-2003).]

  8. #8
    Member
    Join Date
    Mar 2002
    Location
    Quebec, Quebec, Canada
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    With a MySQL database you can return random rows by adding the following to your query :

    ... order by Rand() LIMIT " + str(iNbRecords)

    where iNbRecords is the limit number of record to be returned (if necessary).

    ------------------
    Remy
    Remy

  9. #9
    Senior Member
    Join Date
    Jul 1999
    Location
    Bellingham, WA USA
    Posts
    1,323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    Remy,

    Interesting that you should post this response. I just made the decision to toss the MSAccess database and convert everything to MySQL. We have found each ~transaction~ to access the data takes between 100 to 200 milliseconds with MySQL where it takes over 5 seconds with Access. Therefore to run this entire set of tests, (what should be a 4-6 hour run) is going to take 90+ hours with Access.

    On the other issue, I've figured out how to pull the data from each record in the list of record. I don't need responses to that.

    Thanks all. Your responses have been exceedingly valuable!

    ------------------


    [This message has been edited by DJGray (edited 03-24-2003).]

  10. #10
    Senior Member
    Join Date
    Jul 1999
    Location
    Bellingham, WA USA
    Posts
    1,323
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Random Fetch with SQL

    Remy,

    I'm getting an error on the LIMIT in the query. I've looked at the mySQL reference and don't find this in there.

    Here is the query string up to that point.

    Can you massage it?

    I've tried a number of variations on your suggestion and cannot get any of them to be accepted as valid query strings.

    Thanks!

    ------------------

 

 
Page 1 of 2 12 LastLast

Posting Permissions

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

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:25 AM.

Copyright BetaSoft Inc.