Returning Records around a specified ranked Record

Returning Records around a specified ranked Record

Sometimes, for example when you are dealing with a large table of data that you wish to have ranked, such as a sports league table, you may wish to return the ranking of a particular record, together with some records ranked just before this record, and some just after.

There is an article on the Microsoft Knowledge Base that deals with how to rank Records, but this does not deal with this sort of situation. However, you can adapt the SQL given in that article. Assuming that you have a table, tblPlayer, with fields PlayerID, PlayerName and PlayerPoints, the basic SQL to rank all records in the table would look like this:

SELECT
    P.PlayerID,
    P.PlayerName,
    P.PlayerPoints
    (
        SELECT
            COUNT(*)
        FROM
            tblPlayer
        WHERE
            PlayerPoints>P.PlayerPoints;
        )+1 AS Rank
FROM
    tblPlayer AS P
ORDER BY
    P.PlayerPoints DESC;

And the SQL that you would use would look like this to return the records immediately above and below the player who has a PlayerID of 10 would be:

SELECT
    P.PlayerID,
    P.PlayerName,
    P.PlayerPoints,
    (
        SELECT
            COUNT(*)
        FROM
            tblPlayer
        WHERE
            PlayerPoints>P.PlayerPoints;
    )+1 AS Rank
FROM
    tblPlayer AS P
WHERE
    (
        SELECT
            COUNT(*)
        FROM
            tblPlayer
        WHERE
            PlayerPoints>P.PlayerPoints;
    )+1
BETWEEN
    (
        SELECT
        (
            SELECT
                COUNT(*)
            FROM
                tblPlayer
            WHERE
                PlayerPoints>P.PlayerPoints;
        )+1
        FROM
            tblPlayer AS P
        WHERE
            PlayerID=10;
    )-1
AND
    (
        SELECT
        (
            SELECT
                COUNT(*)
            FROM
                tblPlayer
            WHERE
                PlayerPoints>P.PlayerPoints;
        )+1
        FROM
            tblPlayer AS P
        WHERE
            PlayerID=10;
    )+1
ORDER BY
    P.PlayerPoints DESC;

However, if there is a tie on either the records ranked immediately before the record that you are using, or the record that you are using, you will find that this approach does not work properly. For example, supposing that the record that we want is ranked 15, but the previous record is involved in a tie with another record. Both of these preceeding records are ranked at 13, meaning that there is no record ranked 14, so there will be no records returned preceeding the record that we are after. Having said that, most leagues will have a mechanism for ensuring that nobody is ranked jointly with anybody else, even if this is decided by the toss of a coin.

Top

Last modified at 06/06/2006 15:00:05