Troy Troy - 1 year ago 84
SQL Question

Group and Rank Rows in Mysql

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?

Answer Source

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.


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download