MySQL: LIMIT by a percentage of the amount of records?

Let's say I have a list of values, like this:

id  value
----------
A   53
B   23
C   12
D   72
E   21
F   16
..

I need the top 10 percent of this list - I tried:

  SELECT id, value 
    FROM list
ORDER BY value DESC
   LIMIT COUNT(*) / 10

But this doesn't work. The problem is that I don't know the amount of records before I do the query. Any idea's?

Best answer I found:

SELECT*
FROM    (
    SELECT list.*, @counter := @counter +1 AS counter
    FROM (select @counter:=0) AS initvar, list
    ORDER BY value DESC   
) AS X
where counter <= (10/100 * @counter);
ORDER BY value DESC

Change the 10 to get a different percentage.

In case you are doing this for an out of order, or random situation - I've started using the following style:

SELECT id, value FROM list HAVING RAND() > 0.9

If you need it to be random but controllable you can use a seed (example with PHP):

SELECT id, value FROM list HAVING RAND($seed) > 0.9

Lastly - if this is a sort of thing that you need full control over you can actually add a column that holds a random value whenever a row is inserted, and then query using that

SELECT id, value FROM list HAVING `rand_column` BETWEEN 0.8 AND 0.9

Since this does not require sorting, or ORDER BY - it is O(n) rather than O(n lg n)

You can also try with that:

SET @amount =(SELECT COUNT(*) FROM page) /10;
PREPARE STMT FROM 'SELECT * FROM page LIMIT ?';
EXECUTE STMT USING @amount;

This is MySQL bug described in here: http://bugs.mysql.com/bug.php?id=19795

Hope it'll help.