MySql randomize the last 10 rows

问题:

I need help on how to randomize the last 10 rows of MySql records.

$mysqld = mysql_query(select * from table where amount > amount2 and code = '$code' order by time DESC limit 1);

From the statement above I need to randomize the last 10 rows ordered by time but limited only 1 to display.

EDIT: In other words, I need to have the table ordered by time and then I need to focus on the last 10 rows. From these last 10 rows, I need to pick one and it must be random, which one I get.

Is this possible?

Thanks

回答1:

Assuming that time is the time when record was inserted, this will get you the latest 10 rows from the table:

SELECT * FROM `table` WHERE `amount` > `amount2` AND `code` = '$code'
  ORDER BY `time` DESC LIMIT 10

Now, you can use the result as a temporary table, sort it randomly (as it's only 10 rows) and return one row:

SELECT * FROM (
  SELECT * FROM `table` WHERE `amount` > `amount2` AND `code` = '$code'
    ORDER BY `time` DESC LIMIT 10
) AS temptable 
ORDER BY RAND()
LIMIT 1

回答2:

Try....

SELECT * FROM (SELECT * FROM yerTable ORDER BY id DESC LIMIT 10) AS tmp ORDER BY RAND() LIMIT 1

Obviously replace id with any other distinct column if preferred.