Not sure how to go about doing this query

Discussion in 'MySQL' started by alphamonk, Nov 28, 2010.

0
  1. #1
    I have a simple table with 5 columns col1,col2,col3,col4,clicks.

    What I am trying to do is search my mysql db and return the lines with the least amount of clicks but I want the output to be random and not straight ascending

    This was my first attempt.
    select distinct col1,col2,col3,col4,clicks from my_db order by clicks asc limit 25
    The problem with this query is I get the the same exact lines returned every time because I have 5000 lines where clicks are 0.

    This was my second attempt
    select distinct col1,col2,col3,col4,clicks from my_db where clicks = 0 order by rand() limit 25
    The problem with this query is that at some point all the entries will eventually have a click and at that time this query breaks.

    What I would really like to find is a query similar to this that actually worked
    select distinct col1,col2,col3,col4,clicks from my_db order by clicks ASC order by rand() limit 25

    Thanks for your help
     
    alphamonk, Nov 28, 2010 IP
  2. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #2
    I think this should work:

    SELECT col1,col2,col3,col4,clicks FROM my_db ORDER BY clicks ASC, rand() LIMIT 25;
     
    plog, Nov 28, 2010 IP
  3. alphamonk

    alphamonk Peon

    Messages:
    116
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    That is exactly what I was looking for thank you very much.
     
    alphamonk, Nov 29, 2010 IP
  4. iama_gamer

    iama_gamer Active Member

    Messages:
    404
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    60
    #4
    You can use as fields in the order by. They will be taken in that order so in your case clicks, rand()
     
    iama_gamer, Dec 4, 2010 IP
  5. Dwaighty

    Dwaighty Peon

    Messages:
    358
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I don't think that is what you're looking for because it will not randomize the result by much if your clicks are distinct. The second field of the order by will only be used when the number of clicks is the same. I suggest using a subselect:

    SELECT col1,col2,col3,col4,clicks
    FROM (
    SELECT col1,col2,col3,col4,clicks
    FROM `my_db`
    ORDER BY clicks ASC
    LIMIT 25
    ) AS tbl
    ORDER BY RAND( )
     
    Dwaighty, Dec 6, 2010 IP