MySQL: LIMIT on a percentage of the number of records?

Question

Let's say I have a list of values, for example:

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 that does not work. The problem is that I do not know the number of records before making a request. Any idea?

Answer 1

The 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 10 to get a different percentage.

Answer 2

In case you do this because of an order or a random situation - I started using the following style:

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

If you need this to be random, but manageable, you can use seed (PHP example):

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

Finally - if this is something like something you need to control completely, you can actually add a column that contains a random value whenever a row is inserted, and then query using this

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

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

Answer 3

You can also try:

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

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

Hope this helps.

Answer 4

I have an alternative that was not mentioned in other answers: if you access from any language in which you have full access to the MySQL API (i.e. not the MySQL CLI), you can run the query, ask how many lines will and will interrupt the cycle if this time.

eg. in Python:

 ... maxnum = cursor.execute(query) for num, row in enumerate(query) if num > .1 * maxnum: # Here I break the loop if I got 10% of the rows. break do_stuff... 

This only works with mysql_store_result() , not mysql_use_result() , as the latter requires that you always accept all the necessary rows.

OTOH, the traffic for my solution may be too high - all lines must be transmitted.