Leaderboard, rank query, how to return rows above / below user level

Based on yours :=

, I'm assuming you are using PostgreSQL, right? I'm more familiar with the T-SQL syntax; but independently, both PostgreSQL and T-SQL have windowing functions. You can implement something similar to the following (I left the variables to fill):

$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 create a generic table expression titled scoreOrder

and project a column RowNum

for your grades. In short, it ROW_NUMBER() OVER (ORDER BY score DESC, uniqueID DESC)

simply says, "I am returning the line number of this record, sorted by score

and uniqueID

in both descending and sequential order." Then you will join the CTE with you ... ms

will be your account that you agree with and join it with the help of ns

where ns.RowNum

will be in between your ms.RowNum

, plus or minus of your offset.

There are a ton of other window functions out there. Here are some others that may be more or less suitable for your scenario:

• ROW_NUMBER()
  
  - record log number
• RANK()
  
  - the rank of the record, duplication in links and includes spaces (i.e. if the link is 2nd place, you will have 1, 2, 2, 4th)
• DENSE_RANK()
  
  - the same as rank, except that it fills in the blanks (i.e. if the 2nd place links you have 1, 2, 2, 3).

For more information check the PostgreSQL documentation on window functions and their tutorial

Update:

It is not clear that MySQL does not support window functions or generic table expressions. In your scenario, you will need to put the results of your previous query into a temporary table and then make a similar join as shown above. 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's a SQLFiddle for an example. (I am not using a temporary SQLFiddle table because you need to create tables in the build diagram window).