So there is a match_id, batsman, and batsman_runs column, batsman_runs column consist of values where he scored a number of runs in a ball like 0, 1, 2

See more:

SQL

SQL-Server

MySQL

spark

So there is a match_id, batsman, and batsman_runs column, batsman_runs column consist of values where he scored a number of runs in a ball like 0,1,2,3,4,6. I need to find which batsman scored most of the number of runs in every match in Spark-SQL

What I have tried:

I can find the number of runs a batsman scored in every match by
```
select b.match_id,b.batsman,sum(b.batsman_runs) from balls as b
inner join matches m on b.match_id=m.match_id
where m.method='D/L' 
group by b.match_id, b.batsman
order by sum(b.batsman_runs) desc;
```

The result 


|match_id|     batsman|sum(batsman_runs)|


|  980999|     V Kohli|              113|

|  829743|   DA Warner|               91|

|  392214|   ML Hayden|               89|

| 1136578|     CA Lynn|               74|

|  336025| Salman Butt|               73|

|  980999|    CH Gayle|               73|

| 1136592|     RR Pant|               69|

| 1136592|  JC Buttler|               67|

| 1136578|    CH Gayle|               62|

| 1136578|    KL Rahul|               60|

But I need the top scorer of every match, how can I get that?They are similar match_id in result set