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

Question

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

Possible duplicate of [MySQL: LIMIT by a percentage of the amount of records?](http://stackoverflow.com/questions/5615172/mysql-limit-by-a-percentage-of-the-amount-of-records) — Grzegorz Górkiewicz, Feb 03 '17 at 21:52

score 0 · Accepted Answer · edited May 23 '17 at 12:01

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

Thank you! I have accepted your answer and I will award it in 14 hours — EnexoOnoma, Feb 06 '17 at 10:34

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

1 Answers1