A query to pick out the highest ten values, or the highest 10%, from groups within a table

I have a database of a large number of users, logging where they live and how many times they have logged into the site. I'm looking to find out the most active users in each city.

I know how to find the single most active user in each city:

select user_id, city, max(login_count)
from user
group by city 

But it would be very useful to be able to find the 10 most active users for each city. Or even, more usefully, but I suspect maybe not possible in SQL, the top 10% in terms of activity.

If开发者_如何学Go anyone could give an idea of how to complete either, or both tasks, I'd greatly appreciate it.

I'm using Navicat to connect to a MySQL server for this task.

if you are using MySql you can use the SELECT TOP 10 PERCENT statement.

like

select top 10 percent * from (
    select user_id, city, max(login_count)
    from user
    group by user_id,city
    order by max(login_count)) 

You can do the top 10 as:

select user_id
     , city
     , login_count
  from user
 group by user_id
     , city
 order by city
     , login_count desc
 limit 10

I wouldn't want to run that query very often because it will be quite slow as will require a filesort on a temp table if you were to look at the explain plan.