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

    Join Date
    Oct 2012
    Posts
    10
    Rep Power
    0

    Ranking based from points


    Hi everyone,

    I need some help in ranking the data based on the points calculated. I've tried using the mysql user defined approach but its not working. what it seems to be doing is getting the row id and incrementing it by one. Any help will greatly be appreciated. I've added it to sql fiddle http://sqlfiddle.com/#!2/f6f12/5
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Catania ITALY
    Posts
    3
    Rep Power
    0

    a possible solution


    Code:
    SET @rownum := 0;
    SET @P := 0;
    SET @GP := 0;
    SELECT Ti,Tm,P,GP,IF(@P=P AND @GP=GP , @rownum, @rownum:=@rownum+1) as rank,@P:=P,@GP:=GP
    FROM
    (
    SELECT
        t.teamid AS Ti
        ,t.teamname AS Tm
         
        , (SUM(CASE WHEN (
            g.hometeam = t.teamid AND g.homescore > g.awayscore
        ) OR (
            g.awayteam = t.teamid AND g.awayscore > g.homescore
        ) THEN 3 ELSE 0 END) + SUM(CASE WHEN (
            g.hometeam = t.teamid OR g.awayteam = t.teamid
        ) AND g.homescore = g.awayscore THEN 1 ELSE 0 END)) AS P
         
         
        , SUM(CASE WHEN (
            g.hometeam = t.teamid OR g.awayteam = t.teamid
        ) THEN 1 ELSE 0 END) AS GP
    
       
       
              
    FROM teams t
    LEFT JOIN fixtures g
    ON t.teamid IN (g.hometeam,g.awayteam)
    GROUP BY t.teamname
      ) as nest1
    ORDER BY P DESC, GP DESC

    Comments on this post

    • willr agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    10
    Rep Power
    0
    thanks, works great! Do you know how to get the previous rank?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    10
    Rep Power
    0
    anyone?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Catania ITALY
    Posts
    3
    Rep Power
    0

    FIXTURE the table does not have the appropriate field.


    It would require a field "day in the previous" in the fixture's table . How to distinguish the game otherwise?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Location
    Catania ITALY
    Posts
    3
    Rep Power
    0

    perhaps


    Code:
    SET @rownum := 0;
    SET @P := 0;
    SET @GP := 0;
    SET @rownumP := 0;
    SET @PP := 0;
    SET @GPP := 0;
    SELECT Ti,Tm,P,PP,GP,GPP,IF(@P=P AND @GP=GP , @rownum, @rownum:=@rownum+1) as rank,@P:=P,@GP:=GP
    ,IF(@PP=PP AND @GPP=GPP , @rownumP, @rownumP:=@rownumP+1) as rankP,@PP:=P,@GPP:=GPP
    FROM
    (
    SELECT
        t.teamid AS Ti
        ,t.teamname AS Tm
         
        , (SUM(CASE WHEN (
            g.hometeam = t.teamid AND g.homescore > g.awayscore
        ) OR (
            g.awayteam = t.teamid AND g.awayscore > g.homescore
        ) THEN 3 ELSE 0 END) + SUM(CASE WHEN (
            g.hometeam = t.teamid OR g.awayteam = t.teamid
        ) AND g.homescore = g.awayscore THEN 1 ELSE 0 END)) AS P
         , 
         (SUM(CASE WHEN ( t.teamid=mf.team and g.FixtureID!=mf.maxf)  AND ((
            g.hometeam = t.teamid AND g.homescore > g.awayscore
        ) OR (
            g.awayteam = t.teamid AND g.awayscore > g.homescore
        )) THEN 3 ELSE 0 END) + SUM(CASE WHEN ( t.teamid=mf.team and g.FixtureID!=mf.maxf) AND (
            g.hometeam = t.teamid OR g.awayteam = t.teamid
        ) AND g.homescore = g.awayscore THEN 1 ELSE 0 END)) AS PP
         
        , SUM(CASE WHEN (
            g.hometeam = t.teamid OR g.awayteam = t.teamid
        ) THEN 1 ELSE 0 END) AS GP
       , SUM(CASE WHEN ( t.teamid=mf.team and g.FixtureID!=mf.maxf) AND (
            g.hometeam = t.teamid OR g.awayteam = t.teamid
        ) THEN 1 ELSE 0 END) AS GPP
    
       
       
              
    FROM teams t
    LEFT JOIN fixtures g
    ON t.teamid IN (g.hometeam,g.awayteam) 
    LEFT JOIN
    (
    	SELECT team,max(fixtureid) as maxf
    	FROM
    	(
    	SELECT fixtureid,hometeam as team FROM Fixtures
    	UNION
    	SELECT fixtureid,awayteam as team FROM Fixtures
    	) as nest3
    	GROUP BY team
    )as mf
    on
    t.teamid=mf.team
    
    GROUP BY t.teamname
      ) as nest1
    ORDER BY P DESC, GP DESC
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    10
    Rep Power
    0
    thanks for the help. the only problem with the previous solution is that the previous ranking isn't being calculated correctly, any ideas?

IMN logo majestic logo threadwatch logo seochat tools logo