MySQL Limit number of records to 5 unique values in one column

Question

i am trying to create a leaderboard based on numbers of kills in a game where it shows top 5 ranking players (people with the same number of kills are in the same rank).

How do i get all records up until rank 5 position (all records until a max of 5 unique values in a column(kills)).

I hope that makes sense.

The query will also need to take into account the map. For Example: WHERE map = 'map_name'

For Example:

Table:

Name, Map, Kills
user0 - map2 - 30
user1 - map1 - 30
user2 - map1 - 27
user3 - map1 - 54
user4 - map1 - 34
user5 - map1 - 34
user6 - map1 - 27
user7 - map1 - 22
user8 - map1 - 22
user9 - map1 - 31
user10 - map1 - 21
user11 - map1 - 27
user12 - map2 - 34

Result Needed (all players / records within the top 5 ranks with the map name map1):

1. user3 - 54
2. user4 - 34
   user5 - 34
3. user9 - 31
4. user1 - 30
5. user2 - 27
   user6 - 27
   user11 - 27

So far i have: SELECT * FROMrecordsWHEREmap= 'map1' ORDER BYkillsDESC

but i need to limit the number of rows returned to only the amount needed for the top 5 ranks, rather then all the rows.

Answer 1

try this query :

     SELECT * FROM records WHERE 
    map = 'map1' and  kills in (SELECT * FROM 
(select Kills from records  where map = 'map1' group by Kills order by kills desc limit 0,5) 
as x)  order by Kills desc

SQLFIDDLE