#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    3
    Rep Power
    0

    Get random rows 6 times allowing for duplicates [SOLVED]


    Hey

    I have a basic query which selects 6 random rows but i want it to allow the chance to select the same row more than once.

    This is the current query:

    Code:
    SELECT * FROM winners ORDER BY RAND() LIMIT 6
    The only option i can currently think of is to do 6 queries, but that seems awfully inefficient some times i might want more than 6 rows returned.

    Does mySQL have a method to do this kind of randomisation?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Most (all?) database systems would not do this in a simple/efficient manner, mainly as it is normal to only return a row in a resultset once as that is what is usually (always?) wanted.
    One way (and someone - Rudy, you listening? - may have a better way), would be to have 6 queries linked with UNION ALLs, each being as you have, but using LIMIT 1 instead:
    Code:
    SELECT * FROM winners ORDER BY RAND() LIMIT 1
    UNION ALL
    SELECT * FROM winners ORDER BY RAND() LIMIT 1
    UNION ALL
    SELECT * FROM winners ORDER BY RAND() LIMIT 1
    UNION ALL
    SELECT * FROM winners ORDER BY RAND() LIMIT 1
    UNION ALL
    SELECT * FROM winners ORDER BY RAND() LIMIT 1
    UNION ALL
    SELECT * FROM winners ORDER BY RAND() LIMIT 1
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    Originally Posted by SimonJM
    (and someone - Rudy, you listening? - may have a better way)
    randomness is notoriously problematic

    the RAND() function even moreso

    sorry i could not be more helpful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2008
    Posts
    3
    Rep Power
    0
    Well i can change rand by using PHP rand and finding the nth row

    Such as
    Code:
    <?php $nth = rand(1,$total_rows); ?>
    I was just looking for a way to do it all in one query rather than loop 6 times doing 6 queries.

    UNION does seem to be the best way! Thanks!
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Originally Posted by r937
    randomness is notoriously problematic

    the RAND() function even moreso

    sorry i could not be more helpful
    No worries, I was pretty sure there wasn't an obscure feature that'd permit this!
    However, whilst we are in this vicinity ... bearing in mind all the unknowns here (table size, number of rows, etc.) in a hypothetical, generic kind of fashion, might it be more efficient to alter the SELECTs in the UNION ALLs to be just return the primary key of the table , then use the returned results in a SELECT * WHERE prim_key IN ...?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    Originally Posted by SimonJM
    ... then use the returned results in a SELECT * WHERE prim_key IN ...?
    you mean like WHERE pk IN ( 2, 5, 3, 9, 3, 7 )?

    unfortunately this won't return the same id more than once, which was the premise of this post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,442
    Rep Power
    1688
    Originally Posted by r937
    you mean like WHERE pk IN ( 2, 5, 3, 9, 3, 7 )?

    unfortunately this won't return the same id more than once, which was the premise of this post
    *slaps forehead* D'oh
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo