Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    2

    Question Unanswered: Selecting a random record from top 100 records

    Hope someone can help me with the following issue:

    I would like to randomly pick up a row from a table. However the row that is randomly selected should be from the most recently (say, 50 or 100) posted entry in the table.

    I was able to randomly pick a record (through rand()) or sort the top entries (through limit). However I am not able to combine both the above requirements.

    Thanks in advance for your suggestions!
    --

  2. #2
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    SELECT * FROM (SELECT * FROM tab ORDER BY rank LIMIT 100) x ORDER BY RAND() LIMIT 1

    The subselect returns the 100 "top" rows. The outer select returns one of the rows.

  3. #3
    Join Date
    Mar 2004
    Posts
    480
    I'd modify the above, there is no sense selecting all the data in the inner query. just select your primary key.

    Code:
    select
    foo,
    bar,
    qux
    from
    (select primarykeycolumn from your table order by rank limit 100) x
    order by rand()
    limit 1

  4. #4
    Join Date
    Jan 2007
    Posts
    2

    Cool Selecting a random record from top 100 records

    Thanks snorp & guelphdad for your suggestions.

    It is now working beautifully!

    --

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by guelphdad
    I'd modify the above, there is no sense selecting all the data in the inner query. just select your primary key.
    huh? come again please?

    if the subquery selects only the pk, how can you possibly select any other columns in the outer query?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    ooops, I was in a hurry and was thinking of two different things at once. Yes the above from snorp makes sense. I was thinking of it like this I guess (which doesn't seem any faster of course):

    Code:
    select
    id,
    foo,
    bar,
    qux
    from yourtable
    where id in (select id from yourtable order by rank limit 100)
    order by rand()
    limit 1
    and of course didn't end up posting that.

Posting Permissions

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