Find a users Rank

[MasterACE14]

I'm ranking users on...

 

SELECT `username` FROM `accounts` ORDER BY `strike`+`defence`+`covert` ASC

 

Now that's fine for generating a list of users and simply using a counter in PHP to assign each user a number, but how can I find the rank for 1 user in particular?

 

Thanks,

Ace

[The Little Guy]

Try this:

 

SELECT `username`, (`strike`+`defence`+`covert`) AS rank FROM `accounts` ORDER BY  rank

[teynon]

There might be a better way, but one way would be to SELECT count(*) FROM `accounts` WHERE `strike`+`defence`+`covert` > 'THISUSERSCORE'

 

That will require multiple queries.

 

1) Get the users score.

2) Count the number of results that have a higher score.

[The Little Guy]

I actually take back my previous post, and think you should do this:

 

set @m:=0;
select * from (SELECT member_id, (v1+v2+v3) AS total, @m:=@m+1 AS rank from members order by total desc) as tbl where member_id = 2;

[MasterACE14]

I actually take back my previous post, and think you should do this:

 

set @m:=0;
select * from (SELECT member_id, (v1+v2+v3) AS total, @m:=@m+1 AS rank from members order by total desc) as tbl where member_id = 2;

 

that's done the trick, thanks for your help guys!

 

Appreciated.

 

Kind Regards,

Ace