LIMIT and RAND()

[mcmuney]

Is it possible to use a limit and rand() combination? If yes, how would I use it? First, I'd like to pull x number of results (LIMIT x), which should bring x results, I then would like to display one of the x results randomly.

[realjumper]

I use the above like this....

[code]

$result = mysql_query("SELECT id,item2,item3,item4,item5,item6 FROM table ORDER BY RAND() LIMIT 0,5")
or die(mysql_error());

[/code]

Hope that helps :)

[mcmuney]

Oops, forgot something. I also need to order it by something, so my code would look like this:

[code]

$result = mysql_query("SELECT * FROM table ORDER BY x LIMIT 0,5")
or die(mysql_error());
[/code]

I would then want to apply a rand() display, which will display 1 random from the 5 results.

[realjumper]

Well you're ORDERing the results by RAND().....so you can't also order it by something else as well I don't imagine

[mcmuney]

Hmmm, well, I need to order it by "x" because I want to pull the top 5 results based on "x", then display only 1 randomly from the 5. Is there another way to do this?

[realjumper]

Perhaps you could put the results of the query into an array, then have the array randomoze it?

I'm not 100% sure about this, but I think it would work.

[sgiandhu]

I used the following recently on a site, which allowed me to choose the most recent items and display them randomly:

[code]SELECT tblName.item FROM tblName WHERE MONTH(itemDate) = MONTH(CURDATE()) AND YEAR(itemDate) = YEAR(CURDATE()) ORDER BY RAND()  LIMIT 0, 5[/code]

I used WHERE instead of ORDER to select the items I wanted.

HTH.

Joss

[fenway]

I wonder if LIMIT can take an expression... if so, you can use it to pull a random of the 5.

[shoz]

If you're using MYSQL 4.1 or higher you can try something similar to the following as well.
[code]
SELECT
*
FROM
(
    SELECT
    *
    FROM
    tablename
    ORDER BY x
    LIMIT 5
) AS t
ORDER BY RAND()
LIMIT 1
[/code]