Rank with same result

[sp0rk]

Here's something I use PHP to compute, but I'd like to know if it's possible to get the same result from using mysql instead.

If I have some data like this:

Person | Wins

Alice | 4
Bob | 2
Charlie | 4

The problem is I'd like to have the same rank if two or more persons score equally, like this.

Rank | Person | Wins

1 | Alice | 4
1 | Charlie | 4
2 | Bob | 2

Note that it's the actual number that's of intrest, sorting it in this order isn't the problem.

Any insight into how this is done would be appreciated, if it's even possible.

[tomhath]

I'd be surprised if it can be done in SQL. As far as I know, a row in the result set dosn't know anything about the other rows returned.

[sp0rk]

That pretty much what I'm thinking as well, but my opinion doesn't count for much having all of two weeks experience of databases...

The reason I'm asking is that the logic seems simple enough: "if result less than old_result increase rank by 1". Which is of course how I do it currently in the php-loop that reads the results from a sql-query.

[tomhath]

SQL itself is nonprocedural, so the idea of loops and variables won't work. Some DBMS' (but not mysql) support Stored Procedures, which would do what you want. They give you a procedural language inside the database engine, basically what you are already doing without a lot of the overhead communicating between php and sql.

[Anon]

It can be done in SQL

select person,wins,
(select count(*) from t
where wins > tq.wins ) + 1 as rank
from t tq

[Anon]

GOod solution, I hadn't though of using a subselect. Although I don't believe that's supported in mysql yet.