Search code examples
mysqlsqlsql-limit

How to view only a portion of the table when the LIMIT value is derived from a subquery in Mysql

For table 'Issue'

-Issue

Call_ref is the unique key identifying every call uniquely. Each call is made by a user with users identified through a caller_id. Each user can make multiple calls but every call will have only one caller_id. I want to display calls made by top 20% of active users. I tried this query-

SELECT Caller_id, COUNT(Call_ref) FROM Issue
GROUP BY Caller_id 
ORDER BY COUNT(Call_ref) 
LIMIT round(COUNT(distinct Caller_id)/5)

However, it seems like LIMIT doesn't accept anything but a number. Is there a way I can restrict this view to only top 20% of all records in the resulting table?

Solution

  • With ROW_NUMBER() AND COUNT(*) window functions:

    SELECT Caller_id, Counter
FROM (
  SELECT Caller_id, COUNT(*) Counter,
         ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) rn,
         COUNT(*) OVER () total
  FROM Issue
  GROUP BY Caller_id 
) t
WHERE rn / total <= 0.2
ORDER BY rn