MySQL select top 10% of users

Question

I have a simple MySQL db listing the total time spent each user has spent online in seconds (just a string) per day. I would like to select the top 10% of those users for a given day and return there usernames.

Is there a way to do this in MySQL in a single query..? I've seen examples where you select the top 10 users, but not based on a percentage of total users...

http://stackoverflow.com/questions/4741239/select-top-x-or-bottom-percent-for-numeric-values-in-mysql — Ryan, Jun 11 '13 at 15:00
refactor the string for the time to an integer value; then use ORDER BY and LIMIT — monika mevenkamp, Oct 09 '13 at 00:43

score 0 · Answer 1 · answered Jun 11 '13 at 15:11

0

try this

     SELECT 0.1 * count(username)  as percented_users FROM prices
     GROUP BY username

answered Jun 11 '13 at 15:11

echo_Me

37,078
5
58
78

this just returns 0.1 * the total number of users. I need the top 10% – DavidJB Jun 12 '13 at 10:49

score 0 · Accepted Answer · answered Jun 11 '13 at 15:28

0

Hope this will help you

I used CURRENT_DATE to limit search.

SELECT user_id, date, total_time FROM
(
  SELECT user_id, @rownum:=@rownum+1 AS rownum 
  FROM TABLE_USER , (SELECT @rownum:=0) R
  WHERE date = CURRENT_DATE 
  ORDER by total_time desc
) temp 
where rownum < (select count(*) from TABLE_USER where date = CURRENT_DATE) / 10

answered Jun 11 '13 at 15:28

agarici

602
1
5
9

this does not appear to work, I just get an _mysql_exceptions.OperationalError: (1054, "Unknown column 'user_id' in 'field list'") error, despite the column being in the db – DavidJB Jun 12 '13 at 10:54

score 0 · Answer 3 · edited Feb 27 '18 at 06:19

0

You may try this way:

select * from table limit floor((select count(1) from table)*0.1)

edited Feb 27 '18 at 06:19

tryingToLearn

10,691
12
80
114

answered Feb 27 '18 at 05:38

wong clark

1

MySQL select top 10% of users

3 Answers3