Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: How to fetch random rows for every time you query

    Hi ,
    I constructed an MS access query in java to retrieve the rows in random.

    The code snipet

    Connection con = DriverManager.getConnection( database ,"","");
    Statement s = con.createStatement()
    strQry = "select top 10 S_No , Rnd(S_No) as exp from Questions order by Rnd(S_No) desc" ;
    s.execute(strQry) ;


    This is the query I used to fetch the records in random. When i tried executing this code several times it fetches only the same set of records each time.

    Actually my requirement is , for every time you query this , it should fetch different records.


    Please help me in this.

    Thanks in advance
    Arun kumar

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try ...
    order by rnd(S_No*now());

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    Hi ,
    I tried as you suggested, but it didnt work out. Still i am getting the same rows retrieved multiple times

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by camarun20
    Actually my requirement is , for every time you query this , it should fetch different records.
    Is that the precise requirement or would pseudo random do?

    For example, get your java to generate a random number between 0 and 19.
    Code:
    SELECT top 10 S_No  
    FROM Questions 
    WHERE S_No mod 20 = MyRandomNumber 
    ORDER BY S_No DESC
    Obviously, you can use a different modulus number depending on the number of the rows in the table.

    Gist is - there is no such thing as random in computing. The sort of "random" you are going for is as good as you will get but is a bit tricky when dealing in sets (rather than getting a single value). 99% of the time when one thinks one needs a random number one doesn't strictly need one - then things get easier.
    HTH

    EDIT - actually you can satisfy your requirement by keeping track of MyRandomNumber - don't let it repeat for any request within a session.
    Last edited by pootle flump; 04-01-08 at 06:39.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by camarun20
    Hi ,
    I tried as you suggested, but it didnt work out. Still i am getting the same rows retrieved multiple times
    thats very odd
    it worked fine for me in Office XP,
    admittedly I did have doubts as to how good the randomness of the returned values was. but the oine thign I can say is I didn't get the same rows each time.

    can we see the SQL you were using?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by healdem
    thats very odd
    it worked fine for me in Office XP,
    admittedly I did have doubts as to how good the randomness of the returned values was. but the oine thign I can say is I didn't get the same rows each time.

    can we see the SQL you were using?

    in fact you don't even need the *now() to get psuedo random numbers
    Its possible that JET may not effectivley seed thge random number generator unless you use the *now() construct

  7. #7
    Join Date
    Apr 2008
    Posts
    4
    Quote Originally Posted by healdem
    in fact you don't even need the *now() to get psuedo random numbers
    Its possible that JET may not effectivley seed thge random number generator unless you use the *now() construct



    hi ,

    This is the query i used in my code . and still my was getting the same rows

    select top 10 S_No , Rnd(S_No) as exp from Questions order by Rnd(S_No*now())

    Please let me know whether this query is right?

    Thanks in advance
    Arun

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I can't tell you wheter its right or wrong
    what I used was the equivalent of
    Code:
    select top 10 S_No from Questions order by Rnd(S_No*now())

  9. #9
    Join Date
    Apr 2008
    Posts
    4
    Quote Originally Posted by healdem
    I can't tell you wheter its right or wrong
    what I used was the equivalent of
    Code:
    select top 10 S_No from Questions order by Rnd(S_No*now())
    Hi ,
    If both the queries are identical , how come i am getting the same record while you are not ? This looks strange for me .I dont understand what could be the reason for this ?

    Arun

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As I said, I've had problems with random stuff in set based queries before. There have been a few threads in SQL Server about similar issues.

    What does this return? (interested in the values for rand_no).
    Code:
    select top 10 S_No, Rnd(S_No*now())
     as rand_no from Questions order by Rnd(S_No*now())
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally - I would still take the path of least resistance and use something like the mod technique.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    but they aren't the same...
    you are calling the random function twice. mind you I cant see why that would make a difference

    either take out the "as exp" or change the "order by"

    eg
    Code:
    select top 10 S_No from Questions order by Rnd(S_No*now())
    or
    Code:
    select top 10 S_No , Rnd(S_No) as exp from Questions order by exp
    all I can say is that thsi works fine on my installation, I have my doubts as to how truly random it is... On theface of its it snowehre near as neat as the MySQL RAND function
    what version of Access are you usign (for me its 2002/XP)
    what version of JET are you using (for me its 3.51)

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    rand works for me in 2k3.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just to chime in here but I can't recall if you do or don't need the: Randomize command (you'd put Randomize before your select statement.) Maybe it's just a vb versus vba thing so I'm not really sure if that'll help.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup, I always use Randomize Timer before using any rand commands in code.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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