I'm trying to build a ranking system in a mysql database.
I've found several tutorials on ranking and items here on StackOverflow about ranking individual rows against each other.
However, my issue is that I need to group rows by a user id column, add up the values to a second column grouped by user id, then rank them against other groups of a different user id.
Here's an example of the table I'm using:
user_id km_skied date_entered
1 34 2010-08-19
3 2 2010-08-23
1 3 2010-08-13
4 23 2010-08-01
3 5 2010-08-02
The result printout would be by rank:
Skier Rank:
Rank User ID Total KM 1 1 37 2 4 23 3 3 7
Also, I was wondering how I find the rank for a specific user. Meaning, if I know what the user id is, can I give them just their rank? Like say
"Your Rank: 2 of 345"
That is the second part of this.
Anyone know how to do that? Thanks! Troy
Thanks for your help guys. I was able to come up with an answer based on the following Query:
$totalQuery = "SELECT SUM(track_length) as usertracklength, username, MAX(track_create_time) as lasttrack, count(DISTINCT track_create_time) as totaldays FROM user_tracks GROUP BY username ORDER BY usertracklength DESC";
$totalResult = mysql_query($totalQuery);
$rankResult = mysql_query($totalQuery);
$totalNumEntries = mysql_num_rows($totalResult);
Then Ouputting that to an array
// rank position array
$rankArray = array();
while ($row1 = mysql_fetch_array($rankResult)) {
$rankArray[] = $row1['username'];
}
Then finding position of that username in the array by using a foreach in php
foreach ($rankArray as $rank => $user) {
if ($user == $username) {
$yourRank = $rank+1;
}
}
It's the long way around, but I suppose it works for what I'm going for. Was kind of hoping to get it done within the mysql query for efficiency.
Thanks!
Do the grouping in subquery and ranking of the results (using any of the methods you've found before) in outer query.
Your query should look something like this. Add the ranking logic to the outer loop.
select * from
(select user_id, sum(km_skied) as km from ski group by user_id) x
order by x.km desc;
Don't know if it's an option, but you can use a temporary table for rankings as follows:
create temporary table ranks (rank int primary key auto_increment, user_id int, km int);
insert into ranks (user_id, km)
select user_id, km from (
select user_id, sum(km_skied) as km from ski group by user_id
) x order by x.km desc;
This gives you what you want:
mysql> select * from ranks;
+------+---------+------+
| rank | user_id | km |
+------+---------+------+
| 1 | 1 | 37 |
| 2 | 4 | 23 |
| 3 | 3 | 7 |
+------+---------+------+
3 rows in set (0.00 sec)
One downside to this approach is that skiers who are tied won't get the same rank.
You could try grouping to sum the Km as a first query, then follow it by a correlated subquery to find the ranks. For instance, if your values are stored in a table called "test", sum the Km values into a table called testtbl and then do the ranking.
mysql> select * from test; +------+--------+------+ | Id | km_run | name | +------+--------+------+ | 1 | 34 | a | | 3 | 2 | c | | 1 | 3 | a | | 4 | 23 | d | | 3 | 5 | c | +------+--------+------+ 5 rows in set (0.00 sec)
mysql> create table testtbl as (select Id, sum(km_run) as tot from test group by Id);
Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from testtbl; +------+------+ | Id | tot | +------+------+ | 1 | 37 | | 3 | 7 | | 4 | 23 | +------+------+ 3 rows in set (0.00 sec)
mysql> select t1.Id,t1.tot, ((select count(distinct t2.tot) from testtbl t2 where t1.tot < t2.tot)+1) as Rk from testtbl t1 order by Rk; +------+------+------+ | Id | tot | Rk | +------+------+------+ | 1 | 37 | 1 | | 4 | 23 | 2 | | 3 | 7 | 3 | +------+------+------+ 3 rows in set (0.00 sec)