Click here to Skip to main content
15,887,821 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

SQL
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.
Posted
Comments
Maciej Los 28-Mar-18 2:22am    
Is this question a continuation of this?
Giri Bkm 28-Mar-18 3:45am    
yes, i thought sub-queries are the main issue for the low query speed. that's why i moved on to avoid sub-queries
ZurdoDev 28-Mar-18 8:57am    
I don't think you can.
itsmypassion 28-Mar-18 23:50pm    
You can use CTE instead.
Rajesh Pandya 3-Apr-18 1:31am    
You can use CTE, following link will be helpful:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-2005-common-table-expressions/

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900