From: Peter Brawley
Date: December 11 2007 6:18pm
Subject: Re: Query help, please..
List-Archive: http://lists.mysql.com/mysql/210442
Message-Id: <475ED484.6080703@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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