Say I have a table like ID, NAME, SCORE. Now normally, to get the rankings of the teams, I'd select all and order by. Sometimes though, I don't want to know all the rankings, just the ranking of one team. If I added a column RANK, is there any way for MySQL to automatically fill in those values for me based off of SCORE? (I believe MS Excel has this capability)
and if so, how does it handle ties?
thanks
You can calculate the rankings when you make your query:
SELECT * FROM (
SELECT teams.*, @rownum := @rownum + 1 AS rank
FROM teams, (SELECT @rownum := 0) T1
ORDER BY score DESC) T2
WHERE id = 1
It works by initializing a variable called rownum to 0 and then iterating over the rows in order of decreasing score. For each team the rownum is increased and the team is assigned a rank based on the current value of rownum. The outer select applies a where clause so that only one row is returned.
Here is an improved version that assigns the same rank to teams that have tied scores:
SELECT id, name, teams.score, rank FROM (
SELECT score, @rownum := @rownum + 1 AS rank
FROM (SELECT DISTINCT(score) FROM teams) T1, (SELECT @rownum := 0) T2
ORDER BY score DESC) T3
JOIN teams ON T3.score = teams.score
If this isn't fast enough for you, then use a trigger instead.
Get All Teams:
SELECT
s1.name,
s1.score,
COUNT(s2.name) + 1 AS rank
FROM scores s1
LEFT OUTER JOIN scores AS s2 ON (s1.score < s2.score)
GROUP BY s1.name
ORDER BY COUNT(s2.name)
Get One Team ('The Canucks'):
SELECT
s1.name,
s1.score,
COUNT(s2.name) + 1 AS rank
FROM scores s1
LEFT OUTER JOIN scores AS s2 ON (s1.score < s2.score)
GROUP BY s1.name
HAVING s1.name = 'The Canucks'
ORDER BY COUNT(s2.name)
The method shown in the above examples get the ranking dynamically (not filling a regular or temp table).
Note: both of these assume a given team only exists once in scores table for the rank value to be correct.