Ranking Results

Kerant

Hello,

I've been bugging various people on here for the last couple of weeks with problems with the football prediction website I have been developing, so I wont go into great deal explaining the situation again.

Basically I have a set of users in a database table.
Each user has a set of points.

I can retrieve these results then rank them in a table using the ORDER BY sql clause, but what I would like is some way of recognising what rank each user is based on their results, and have that stored in the database table along with the user's points.

For example:

id--name--points--rank
1--person1--204----4
2--person2--380----1
3--person3--200----3
4--person4--340----2

This way I could ORDER BY the ranked number when I retrieve the data, plus I would be able to state exactly which rank they are in the table when displaying the data.

Does anybody know of a way of doing this please?

Thanks very much.

devinemke

in the interest if database normalization i suggest that you do not store the rank in db and just fetch this dynamically based on the points field.

Kerant

Okay, point taken.

Could somebody suggest a way of giving a ranking number to each result though please?

Thanks

djjjozsi

hello,
You can select the rank from the rank table using the user's point value:

u table:
userid
username
point

rank table:
from
to
rank

 SELECT `u`.`name`,`u`.`point`,`rank`.`rank`
FROM `u`
LEFT JOIN `rank` ON `u`.`point`
BETWEEN `rank`.`from`
AND `rank`.`to`

Kerant

djjjozsi;10905132 wrote:
hello,
You can select the rank from the rank table using the user's point value:

u table:
userid
username
point

rank table:
from
to
rank
 SELECT `u`.`name`,`u`.`point`,`rank`.`rank`
FROM `u`
LEFT JOIN `rank` ON `u`.`point`
BETWEEN `rank`.`from`
AND `rank`.`to`

Thanks for the reply.

I don't understand what the code will actually do?

What data would be stored in the "rank" table?

djjjozsi

in the range table lets set the point ranges

from to rank
0 - 10 beginner
11 - 20 heavy user

ect.

And that "example" query will select the user's point from the rank table's range.

Kerant

djjjozsi;10905134 wrote:
in the range table lets set the point ranges

from to rank
0 - 10 beginner
11 - 20 heavy user

ect.

And that "example" query will select the user's point from the rank table's range.

Ohh I understand.

Sorry I think you misunderstood my original post.

Basically I want to have a number ranking next to each user so I can rank them in a leaderboard.

For example:

1 - user
2 - user
3 - user
4 - user
5 - user
6 - user

djjjozsi

Kerant;10905135 wrote:
1 - user
2 - user
3 - user
4 - user
5 - user
6 - user

its a simple incremented $i variable while you print the users 🙂

Kerant

djjjozsi;10905137 wrote:
its a simple incremented $i variable while you print the users 🙂

Yeah I understand how to do it that way.

But I'd like to have some way of determining the users table ranking based on their points.

For example if you click on a user profile it says:

Username - 1323 Points (Rank 32)

djjjozsi

i know two ways:
in a profile page you can select how many users have higher points then that user.

SELECT name,points,(SELECT COUNT(*) FROM users b WHERE b.points>a.points  ) FROM users a WHERE id=1

this will select with a sub query. If it is equal with 0 then he/she is the number one.
But you must handle the equal points.

the second way is coded in php.