Select
FirstName,
MiddleName,
LastName,
MatchType,
RunsScored
From (
Select
s.Id,
a.FirstName,
a.MiddleName,
a.LastName,
s.MatchType,
s.RunsScored,
Row_Number() Over(Partition By MatchType Order By RunsScored Desc) rn
From
PlayersCareerBatting s Join PlayersBioDataNew a
On s.Id=a.Id
) x
Where rn=1
here is my code. for this code, i'm getting a query speed too slow. so i want to speed up the query. for that, i'm going to avoid the sub-query for that. pls help me how to avoid sub-query to run query faster.
What I have tried:
Select a.firstname, a.middlename, s.matchtype, Max(s.Runsscored) from PlayersCareerBatting s Join PlayersBioDataNew a
On s.Id=a.Id group by s.matchtype,a.firstname, a.middlename
i tried this. but this showing all the rows, i want only the rows who got maximum in runsscored with their respective names.