[RESOLVED] Easiest way getting rank

Abarak

At this point in time I am currently developing a game which consists of multiple characters. Each character will be ranked within the game based upon certain criteria contained within individual columns.

Example.
Col 1 - Col 2 - Col 3 - Col 4
Row 1: 10 10 10 10
Row 2: 50 50 50 50
Row 3: 20 20 20 20

When each value within the column is multiplied by a specific, pre determined value, and added together you get the current player total value.

I was wondering how I can get the players rank. Using the example above Row 2 would be ranked 1, Row 3 would be ranked 2, and Row 1 would be ranked 3rd. I know there has to be a way MySQL can do this for me but I am at a lost as to how.

Any information that can be provided would be most appreciated.

Abarak

bogu

Try this:

SELECT (col1+col2+col3+col4) as rank FROM test ORDER BY rank DESC

Abarak

SELECT (col1+col2+col3+col4) as rank FROM test ORDER BY rank DESC

That would work if I needed to get a list from highest to lowest rank. However, I need to get the rank of a specific character. I would do something along these lines but the character list could be anywhere from 10 - who knows how big.

I just need to get a specific character rank based on everyone one else.

If you need more information just ask.

Abarak

Roger_Ramjet

This one came up last week. Credit Lars Berg with the answer, and me with the modification for this case.

Needs subqueries so you have to have a version that supports them

$sql = "SELECT COUNT(*)+1 AS ranking
     FROM (SELECT (col1+col2+col3+col4) as rank FROM test) AS r
     WHERE rank > 
        (SELECT (col1+col2+col3+col4) as rank 
          FROM test
          WHERE player_id=$pid)";

🙂

Abarak

Works like a charm!

Thanks for the query. 😃