[SOLVED] Ranking

[josborne]

Much like the person who posted this topic, I am trying to find the ranking of a particular ID.

 

The issue I face is that the table has multiple score rows that must be sorted by year, summed and then ranked. I tried modifying the query suggested by kickstart but couldn't get it to work.

 

The table only has three important columns id points and season

 

CREATE TABLE `Results_tbl` (
`Race_ID` int(10) NOT NULL,
`Season` year(4) NOT NULL,
`Rider_ID` int(5) NOT NULL,
`Position` int(2) default NULL,
`Race_Time` time default NULL,
`MSecs` char(5) default NULL,
UNIQUE KEY `Race_ID_2` (`Race_ID`,`Rider_ID`),
KEY `Race_ID` (`Race_ID`),
KEY `Season` (`Season`),
KEY `Rider_ID` (`Rider_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=7575 DEFAULT CHARSET=latin1

 

What I tried was this:

 

SELECT z.*, x.Rank
FROM `Results_tbl` z
INNER JOIN (SELECT a.Rider_ID, SUM(a.Points), @num := @num + 1 AS Rank from `Results_tbl` a, (SELECT @num := 0) d
order by SUM(a.Points)) x
ON z.Rider_ID = x.Rider_ID
WHERE z.Rider_ID = '1'
WHERE a.Season=2009
LIMIT 1

 

And that gave me a result bot it was completely wrong as it the rank was the same as the number of rows in the table.

 

 

 

 

[josborne]

Well, interestinly enough, the topic directly below mine when I posted this gave me the information I needed to come up with a solution on my own.

 

This is what I ended up with:

 

SELECT a.Rider_ID, a.Rank
FROM (

SELECT a.Rider_ID, SUM( a.Points ) AS Score, @num := @num +1 AS Rank
FROM `Results_tbl` a
WHERE a.Season =2009
GROUP BY a.Rider_ID
ORDER BY Score DESC
) AS a
WHERE a.Rider_ID =2