On Dec 11, 2007 8:38 AM, Anders Norrbring <lists@stripped> wrote:
> I'm looking at a situation I haven't run into before, and I'm a bit
> puzzled by it.
> I have this table structure:
> Table USERS: userid, class
> Table OBJECT: userid, class, result
> Now I want to query the database for a certain user's result in a
> specified class, which is very, very easy. No problems.
> But, I also want to find out the user's position relative to others
> depending on the result.
> So, if the specified user's result is the 9:th best of all of the users,
> I want to have a reply from the DB query that say he has position number 9.
> I really can't figure out how to do that... Somehow I have to make MySQL
> calculate the position based on the value in the result column.
Take a look at http://arjen-lentz.livejournal.com/55083.html . Very
similar ideas in play, though you also have a join.
The basic idea is that you do a count on the number of users that have
a lower score.