### Thread: Ranking based from points

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. 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
3. 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?
4. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2012
Posts
10
Rep Power
0
anyone?
5. 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?
6. 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```
7. 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?