I have following df:-
result | state | clubName |
---|---|---|
win | XYZ | club1 |
win | XYZ | club2 |
win | XYZ | club1 |
win | PQR | club3 |
I need state wise max wining clubName
val byState =Window.partitionBy("state").orderBy('state)
I tried creating a window but does not helps..
Expected Result :-
Some like this in sql
select temp.res
(select count(result) as res
from table
group by clubName) temp
group by state
e.g
state | max_count_of_wins | clubName |
---|---|---|
XYZ | 2 | club1 |
club1
andclub2
have the same number of wins. why do you chooseclub1
? and why there is no row for statePQR
in your result? – blackbishopspark.sql
to get it. Your sql isn't actually valid though. Perhaps, it makes sense to start with fixing that. – Dima