#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    13
    Rep Power
    0

    SQL code improvement and RANK()


    Hi,

    I have a website that displays a live team score. There is a table with entries (pga_championship_2013) and a table with scores called (leaderboard_A).

    I have SQL statement but when I run it it takes an age to process. Is there anyway to speed this up by improving the SQL?

    I am also trying to get a RANK value created aswell similar to the Excel function RANK(). I want it to say 1,1,1,4,5,5,7 for example is there's a 3 way tie for first and a 2 way tie for 5th.

    Thanks.

    Code:
    SELECT	t.name,
    	t.player1, s1.to_par,
    	t.player2, s2.to_par,
    	t.player3, s3.to_par,
    	s1.to_par+s2.to_par+s3.to_par as total_score
    
    FROM	pga_championship_2013 as t
    	Left Join leaderboard_A as s1 On t.player1=s1.name
    	Left Join leaderboard_A as s2 On t.player2=s2.name
    	Left Join leaderboard_A as s3 On t.player3=s3.name
    
    WHERE 
    s1.tournament_name = 'PGA Championship' and s1.year = "2013" and
    s2.tournament_name = 'PGA Championship' and s2.year = "2013" and s3.tournament_name = 'PGA Championship' and s3.year = "2013" and
    
    s1.cut = "0" and
    s2.cut = "0" and
    s3.cut = "0" and
    
    s1.wd != "1" and
    s2.wd != "1" and
    s3.wd != "1"
    
    order by total_score asc
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    13
    Rep Power
    0
    MOD's can you please move this to the MySQL fourm I posted it in the wrong one. Thanks
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by crookesa
    I am also trying to get a RANK value created aswell similar to the Excel function RANK(). I want it to say 1,1,1,4,5,5,7 for example is there's a 3 way tie for first and a 2 way tie for 5th.
    mysql does not have that functionality, so i would strongly advise to do this within your application language (php or whatever) while printing out the results

    as for the performance, please do a SHOW CREATE TABLE for each table, and do an EXPLAIN on your query

    by the way, your left joins will never work as left joins, just inner joins (because of the WHERE conditions) so you might as well write them as inner joins
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo