Find Nth Highest Value in SQL using DENSE_RANK()

WITH RankedScores AS (
    SELECT
        player_name,
        score,
        DENSE_RANK() OVER (ORDER BY score DESC) as rnk
    FROM
        player_scores
)
SELECT
    player_name,
    score
FROM
    RankedScores
WHERE
    rnk = 3; -- To find the 3rd highest score

How it works: This snippet uses a Common Table Expression (CTE) named `RankedScores` to first assign a rank to each player's score. `DENSE_RANK()` assigns consecutive ranks to rows within a partition (here, the entire table) based on the `ORDER BY` clause. If scores are tied, they receive the same rank, and the next distinct score gets the next consecutive rank. The outer query then selects all players and their scores that match the desired rank (e.g., the 3rd highest). This method avoids `LIMIT`/`OFFSET` for ranking and correctly handles ties, making it robust for finding specific ranked items.