Pashiamas Pashiamas - 4 years ago 75
MySQL Question

How to limit query results by a percentage of the total number of results in MySQL?

The below mysql question returns only the 10 first rows. How can I limit the them to 10% of all?

SELECT page,
poso,
diff
FROM (SELECT page,
Count(*) AS poso,
( Sum(Date(timestamp) = Curdate()) - Sum(
Date(timestamp) = Date_sub(Curdate(),
INTERVAL 1 day)) )
diff
FROM `behaviour`
WHERE Date(timestamp) >= Date_sub(Curdate(), INTERVAL 1 day)
GROUP BY page
ORDER BY ( Sum(Date(timestamp) = Curdate()) - Sum(
Date(timestamp) = Date_sub(Curdate(),
INTERVAL 1 day))
) DESC
LIMIT 10) AS u
ORDER BY diff DESC

Answer Source

Adapted from the answer to the duplicate question:

SELECT page,
       poso,
       diff
FROM    (
    SELECT *,
           @counter := @counter + 1 AS counter
    FROM   (select @counter:=0) AS initvar, 
           (SELECT page, 
                   Count(*) AS poso, 
                   ( Sum(Date(timestamp) = Curdate()) - Sum( 
                     Date(timestamp) = Date_sub(Curdate(), 
                                       INTERVAL 1 day)) ) 
                            diff
            FROM   `behaviour` 
            WHERE  Date(timestamp) >= Date_sub(Curdate(), INTERVAL 1 day) 
            GROUP  BY page 
            ORDER  BY ( Sum(Date(timestamp) = Curdate()) - Sum( 
                        Date(timestamp) = Date_sub(Curdate(), 
                                   INTERVAL 1 day)) 
                      ) DESC) AS u
) AS v
WHERE counter <= 10/100 * @counter
ORDER  BY diff DESC;

Demo here: http://rextester.com/JKMBZR62923

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download