CODEWITHSUNDEEP
c#phpsqlunity-game-engine
Jason Chung
Jason Chung

Reputation: 127

Leaderboard, rank query, how to return the rows above/below a users rank

Given this query, if I want to pull the rank of a specific individual where I know there $name and $score and return the rows above/below that rank (say +/- 4), how would I go about doing that?

$query = "SELECT @curRank := @curRank + 1 AS Rank,
            uniqueID,
            name,
            score
        FROM scores, (SELECT @curRank := 0) r
        ORDER by score DESC";

I'm coding in php, using MySQL and C# in Unity. My game is making a call to the server and running the php code. Goal is to echo the information and parse the information back in the game.

Any help would be much appreciated :)

Upvotes: 3

Views: 437

Answers (1)

Yeager
Yeager

Reputation: 71

Based off of your :=, I'm assuming you are using PostgreSQL, correct? I'm more familiar with the T-SQL syntax; but regardless, both PostgreSQL and T-SQL have windowing functions. You could implement something similar to the following (I left out variables for you to fill-in):

$query = "WITH scoreOrder
    AS
    (
        SELECT uniqueID,
            name,
            score,
            ROW_NUMBER() OVER (ORDER BY score DESC, uniqueID DESC) AS RowNum
        FROM scores
        ORDER BY uniqueID DESC
    )
    SELECT ns.*
    FROM scoreOrder ms --Your matching score
    INNER JOIN scoreOrder ns --Your nearby scores
    ON ms.name = /* your name variable */
        AND ms.score = /* your score variable */
        AND ns.RowNum BETWEEN ms.RowNum - /* your offset */ and ms.RowNum + /* your offset */";

Explanation: First, we're creating a common table expression called scoreOrder, and projecting a RowNum column for your scores. In short, ROW_NUMBER() OVER (ORDER BY score DESC, uniqueID DESC) is just saying, "I am returning the row number of this record ordered by score and uniqueID, both descending and in that order." Then, you join that CTE with itself... ms will be your score that you match with, and you join that with ns where the ns.RowNum will be between your ms.RowNum, plus or minus your offset.

There are a ton of other windowing functions. Here are some others that could be more or less appropriate for your scenario:

  • ROW_NUMBER() - the rownumber of the record
  • RANK() - the rank of the record, duplicating in ties and includes gaps (i.e., if 2nd place ties, you would have 1st, 2nd, 2nd, 4th)
  • DENSE_RANK() - same as rank, except that it fills in the gaps (i.e., if 2nd place ties, you would have 1st, 2nd, 2nd, 3rd)

For more info, check the PostgreSQL documentation on windowing functions and their tutorial

Update:

Unfornately, MySQL does not support windowing functions or common table expressions. In your scenario, you will have to put the results of your previous query into a temp table, then doing a similar join as demonstrated above. So for example...

CREATE TEMPORARY TABLE IF NOT EXISTS allRankings AS 
(
  SELECT @curRank := @curRank + 1 AS Rank,
        uniqueID,
        name,
        score
    FROM scores, (SELECT @curRank := 0) r
    ORDER by score DESC, uniqueID
);

SELECT r.*
FROM allRankings r 
INNER JOIN allRankings myRank
ON r.Rank BETWEEN myRank.Rank - <your offset>  AND myRank.Rank + <your offset>
    AND myRank.name = <your name> 
    AND myRank.score = <your score> 
ORDER by r.Rank;

Here is a SQLFiddle link for an example. (I'm not using a temp table on SQLFiddle because you have to build tables in the Build Schema window).

Upvotes: 2

Related Questions