The easiest way to select a percentage of the number of rows from a MySQL table?

Question

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

I have a MySQL data table.

Now let's say that this table has 100 rows of data.

In pseudo code:

SELECT data FROM table

Now could one select $_GET['percentage'] random data from a table?

For example (again in pseudo-code):

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

If possible, how can I do this?

Answer 1

In MySQL, this is probably the easiest thing to do in two queries. First get the number of rows in the table:

 SELECT COUNT(*) FROM MyTable; 

Then prepare the query to get random strings:

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

Then execute the prepared request and send the counter value divided by 10.

Not every problem should be solved in one request.

---

Here is 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 have to force the variable to a known format - an integer value in this case. Otherwise, you risk creating a SQL Injection vulnerability.

Answer 2

If you have an auto-added value field, you can use

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

Otherwise, the stored procedure may help.

Answer 3

select a column value from mytable WHERE RAND () <= 0.5 ..... will directly result in almost 50% of the records

Answer 4

Perhaps this event will make a decision.

 drop event OEAuditEvent; DELIMITER $$ CREATE EVENT OEAuditEvent ON SCHEDULE EVERY 1 SECOND STARTS '2012-09-05 09:00:00' DO BEGIN DECLARE a CHAR(20); DECLARE b,c,d INT; DECLARE done INT DEFAULT FALSE; IF CURRENT_TIME() = '23:40:00' THEN begin DECLARE cur CURSOR FOR select OE_User,count(OE_User) from RNCM_Status where date(OE_Date)=CURDATE() group by OE_User; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO a, b; SET c=ceil((b*5)/100); IF done THEN LEAVE read_loop; ELSE insert into OE_Audit(MDN,CAF,UploadedDate,OEUser,OEDate,UserCount,QCCount,intime) select MDN,CAF,UploadedDate,OE_User,OE_Date,b,c,now() from RNCM_Status where OE_User=a and date(OE_Date)=CURDATE() order by rand() limit c; END IF; END LOOP; CLOSE cur; end ; END IF; END $$ DELIMITER ;