Easiest way to choose a percentage of an amount of rows from a MySQL table?

Question

I have a script that has a GET variable: $_GET['percentage']

I have a MySQL table of data.

Now lets say that there are 100 rows of data in this table.

In pseudo-code:

SELECT data FROM table

Now would it be possible to select $_GET['percentage'] of random data from table?

For example (again in pseudo-code):

$_GET['percentage'] = 10;
SELECT 10% of data from table order by rand()

If this IS possible, how could I do it?

Answer 1

In MySQL, it's probably easiest to do this in two queries. First, get the count of rows in the table:

SELECT COUNT(*) FROM MyTable;

Then prepare the query to get random rows:

SELECT ... FROM MyTable ORDER BY RAND() LIMIT ?;

Then execute the prepared query and send the value of the count divided by 10.

Not every problem needs to be solved by a single query.

---

Here's an example PHP script, edited to use the old mysql extension.

<?php

// Get the total number of rows in the table.
$sql = "SELECT COUNT(*) FROM Kingdoms";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$rows_in_table = $row[0];

// We only want a portion of the rows, specified by the user
// choice of percentage.  The count we want is therefore equal
// to the total number of rows in the table multiplied by the
// desired percentage.
$percentage = intval($_GET["percentage"]) / 100.0;
$count = intval(round($rows_in_table * $percentage));

// LIMIT makes the query return at most the number of rows specified.
// Sort randomly first (if the table has too many rows this will be slow),
// then return the first $count rows.
$sql = "SELECT * FROM Kingdoms ORDER BY RAND() LIMIT {$count}";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
  print_r($row);
}

PS: Always be careful when interpolating a variable into an SQL expression. You should force the variable to a known format -- an integer value in this case. Otherwise you risk creating an SQL Injection vulnerability.

Answer 2

If you have auto incremented ID field you may use

HAVING ID_FIELD<=ceil(count(*)*10/100);

Otherwise a stored procedure can help in this.