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.

On Dec 11, 2007 8:38 AM, Anders Norrbring <lists@norrbring.se> 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.

On Dec 11, 2007, at 10:46 AM, Rob Wultsch wrote:

> On Dec 11, 2007 8:38 AM, Anders Norrbring <lists@norrbring.se> 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.

Is there any reason you wouldn't want to count the people in front of
you and add 1 to get your place in line? It seems like depending on
where you are, that may be a shorter number to count

But I don't know anything about how to do stuff off of separate tables
yet still trying to grasp that


>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=japruim@raoset.com
>
>

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
japruim@raoset.com

Anders,

>I also want to find out the user's position relative to others
depending on the result.

For a given pUserID, something like this?

SELECT userid,result,rank
FROM (
SELECT o1.userid,o1.result,COUNT(o2.result) AS rank
FROM object o1
JOIN object o2 ON o1.result < o2.result OR (o1.result=o2.result AND
o1.userid=o2.userid)
GROUP BY o1.userid,o1.result
)
WHERE userid = pUserID;

PB

-----

Anders Norrbring 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.
>
>