Mysql Rank Update

Question

Guys I'm new here and new to MySQL too ..

So I am trying to create a database which manages a team record.The database contains a table named 'team' with a set of columns as follow,

 1. TeamID[Primarykey,A_I]
 2. TeamName[Unique]
 3. TeamWins
 4. TeamLoss
 5. TeamPoints

So the agenda is to rank the teams on the basis of points, the greater the points higher the rank.All I want to do is take TeamName,TeamWins,TeamLoss and TeamPoints as input via php, and as the TeamPoints increase/decrease of a team its supposed to move the rank upwards/downwards and display a Ranking table.

Help me Please, Thanks!

`TeamRank` doesn't look like any of your table columns, that will be computed on runtime in `PHP` — Hanky Panky, Dec 09 '13 at 08:59
Yea I forgot to mention the TeamRank column, I tried to compute it on runtime but I was facing issues updating it on the Database. — Zain Sohail, Dec 09 '13 at 09:03
@SureshKamrushi It just gives me a output of the Database with respect to TeamPoints ... — Zain Sohail, Dec 09 '13 at 09:06
possible duplicat : http://stackoverflow.com/questions/20021105/mysql-query-to-get-the-row-position-in-a-table — Suresh Kamrushi, Dec 09 '13 at 09:13

Kickstart · Answer 1 · 2013-12-09T09:37:29.397

2

You can get the rank in a piece of SQL something like this:-

SELECT TeamID,
        TeamName,
        TeamWins,
        TeamLoss,
        TeamPoints,
        @Rank := @Rank + 1 AS TeamRnk
FROM SomeTable
CROSS JOIN (SELECT @Rank:=0) Sub0
ORDER BY TeamPoints DESC

If you wanted the rank of a particular team then you could expand that to:-

SELECT TeamID,
        TeamName,
        TeamWins,
        TeamLoss,
        TeamPoints,
        TeamRnk
FROM
(
    SELECT TeamID,
            TeamName,
            TeamWins,
            TeamLoss,
            TeamPoints,
            @Rank := @Rank + 1 AS TeamRnk
    FROM SomeTable
    CROSS JOIN (SELECT @Rank:=0) Sub0
    ORDER BY TeamPoints DESC
) Sub1
WHERE TeamID = xxxxx

edited Dec 09 '13 at 09:37

answered Dec 09 '13 at 09:06

Kickstart

21,403
2
21
33

Thanks man, its working on the Database end, but how do I implement this on PHP code. – Zain Sohail Dec 09 '13 at 16:00
This will just you the rank for an individual team, or the first bit for all teams. It will do this at the time of the query, so you can just select the columns you want to display. Not quite sure what it is that you want to display that you can't get by doing a select based on this and looping round. – Kickstart Dec 09 '13 at 16:22
I cannot post the code here, im creating a new Question you can see there – Zain Sohail Dec 09 '13 at 16:34

score 0 · Answer 2 · answered Dec 09 '13 at 09:20

0

Your query:

select @rowRank := @rowRank+1 AS rank, team.* from team
JOIN    (SELECT @rowRank := 0) r
order by TeamPoints desc

Sql Fiddle: http://sqlfiddle.com/#!2/7771e/1

answered Dec 09 '13 at 09:20

Suresh Kamrushi

15,627
13
75
90

Mysql Rank Update

2 Answers2