gloomy.eu

How to make MySQL return a desired percentage of rows?

Some people time after time ask this question. Most of
them are migrating from MsSQL (Microsoft SQL Server 2xxx) that
has the ability to retrieve a desired percentage of rows. For example:

SELECT TOP 50 PERCENT * FROM table

This query will return 50% of rows. Nice. What do you do
in MySQL if you get a task “to return half of the records from a table”?
So far i found just one easy solution for MySQL 5.0.7+ version:

SELECT @percentage := ROUND(COUNT(*) * 50/100) FROM table;
PREPARE STMT FROM 'SELECT * FROM table LIMIT ?';
EXECUTE STMT USING @percentage;