mysql - Returning top 3 count values in sql queries -

i have grouped table based on column field, , simultaneously displayed count value each group. example

user   |    count(user) user 1 |     4 user 2 |     3 user 3 |     3 user 4 |     3 user 5 |     2 

now need display top 3 users count in case user 1, user2, user 3, user 4.

i have written grouping logic when try order values of count desc , restrict result 3(limit 3), user 1,2, , 3. not correct because user 4 comes in top 3 count.

this should it:

select user, count(1) users group user having count(user) >= (select count(1) users group user order count(user) desc limit 2, 1) order count(user)

example table: mysql> select * users; +----+-------+ | id | user  | +----+-------+ |  1 | user1 | |  3 | user3 | |  4 | user4 | |  5 | user5 | |  6 | user1 | |  7 | user2 | |  8 | user2 | |  9 | user2 | | 10 | user2 | | 11 | user1 | | 12 | user5 | | 13 | user5 | | 14 | user6 | | 15 | user6 | | 16 | user6 | +----+-------+ 15 rows in set (0.00 sec)   mysql> select user, count(1) users group user having count(user) >= (select count(1) users group user order count(user) desc limit 2, 1) order count(user) desc; +-------+----------+ | user  | count(1) | +-------+----------+ | user2 |        4 | | user1 |        3 | | user5 |        3 | | user6 |        3 | +-------+----------+ 4 rows in set (0.00 sec) 

so trick figure out 3rd highest count value is. users have count >= (so includes users tied 3rd place).

the subquery limit 2, 1 used 3rd highest count value. use limit 4, 1 if wanted 5 highest instead of 3.