How can I group table by highest record

Question

I have sql results:

+----------+-----+--------+
|rewardID  |count|playerID|
+----------+-----+--------+
|BurgerKing|5    |1       |
+----------+-----+--------+
|BurgerKing|4    |2       |
+----------+-----+--------+
|KFC       |1    |1       |
+----------+-----+--------+
|KFC       |5    |2       |
+----------+-----+--------+

how can i make it to be like

+----------+-----+--------+
|rewardID  |count|playerID|
+----------+-----+--------+
|BurgerKing|5    |1       |
+----------+-----+--------+
|KFC       |5    |2       |
+----------+-----+--------+

I mean select player who get most reward for each reward row

Answer 1

Try the below

select Y.rewardId,Y.count,[playerId] from YourTable Y
join (select [rewardId],MAX([count]) as [count] from YourTable group by [rewardId]) as Y1
on Y.rewardId=Y1.rewardId and Y.[count]=Y1.[count] 

Tried with the following table.

CREATE TABLE [dbo].[yourtable]( [rewardid] [nvarchar](50) NULL, [count] [int] NULL, [playerid] [int] NULL )
INSERT INTO [dbo].[yourtable] ([rewardid] ,[count] ,[playerid])
VALUES ('Burger King', 5,1), ('Burger King', 4,2), ('KFC',1,1), ('KFC',6,2) GO 

And it gave me result

rewardid    count   playerid
Burger King  5      1
KFC          6      2

Answer 2

select yourtable.rewardid,yourtable.count,yourtable.playerid  from yourtable left join (select distinct rewardid, max(count) OVER (PARTITION BY rewardid)  as maxcount from yourtable) source 
on source.rewardid=yourtable.rewardid and source.maxcount=yourtable.count where source.maxcount is not null

This answer is assuming that we want records for each reward id

Answer 3

select t.rewardId,t.count,playerId from tableName as t    
join 
(select rewardId,MAX([count]) as cnt 
 from tableName
 group by rewardId
) as t1
on t.rewardId=t1.rewardId and t.count=t1.cnt