How to select 6 top records of each individual records at the database when selecting from all rows

Question

Assume that i have the following table

CREATE TABLE #tblUsersPokemons (
    RecordId int NOT NULL,
    PokemonId int NOT NULL,
    PokemonExp int NOT NULL,
    PokemonLevel int NOT NULL,
    UserId int NOT NULL
)

Now the below query works awesome as expected

select 
  SUM(cast(PokemonExp as bigint)) as TotalExp,
  MAX(PokemonLevel) as MaxPokeLevel,
  Count(PokemonId) as TotalPoke,
  UserId 
from #tblUsersPokemons 
group by UserId

Here example result of such query

ToplamExp   MaxPokeLevel    TotalPoke   UserId
----------- --------------- ----------- --------
29372294    101             4           1
1134696     98              1           2
1400        98              1           101
24534365    98              4           102
1400        98              1           1102
1400        98              1           1103
1400        98              1           2102
1400        98              1           2103
789220      98              7           2105
1468        98              1           3104

Now here my question comes

I want to limit counted PokemonIds. What i mean is i want to select maximum 6 of each same PokemonId records. And from these records top 6 ordered desc by PokemonExp should be counted in.

For example a user has the below records

From this table the query should take record id : 1,2,3,4,5,6,9 and not take 7,8 since top 6 records for PokemonId 1 taken

Answer 1

If I understand correctly, you want the aggregations on the top 6 rows for each user. You can do this easily using row_number():

select SUM(cast(PokemonExp as bigint)) as ToplamExp,
       MAX(PokemonLevel) as MaxPokeLevel,
       Count(PokemonId) as TotalPoke,UserId 
from (select p.*,
             row_number() over (partition by userid order by pokemanexp desc) as seqnum
      from tblUsersPokemons p
     ) p
where seqnum <= 6
group by UserId;

EDIT:

I think you want to include PokemonId in the partition by clause:

select SUM(cast(PokemonExp as bigint)) as ToplamExp,
       MAX(PokemonLevel) as MaxPokeLevel,
       Count(PokemonId) as TotalPoke,UserId 
from (select p.*,
             row_number() over (partition by userid, PokemonId
                                order by pokemanexp desc) as seqnum
      from tblUsersPokemons p
     ) p
where seqnum <= 6
group by UserId;