Top 3 Scores - MySQL [duplicate]

Question

I'm building a "Top 3 Scores" Leaderboard. I want to display the top three scores, drawing the max per person, but I don't want to limit 3, because I want to display anyone that has the top 3 scores. So for example, with the data below,

+----+-----+
|Name|Score|
+----+-----+
|Matt|   17|
|Mark|   29|
|Luke|   28|
|John|   29|
|Paul|   27|
|Matt|   29|
|Mark|   22|
+----+-----+

I want to display:

+------+-----+
|Name  |Score|
+------+-----+
|1.Matt|   30|
|2.Mark|   29|
|2.John|   29|
|3.Luke|   28|
+------+-----+

My first thought is to extract the max for everyone, and then stop displaying after the score changes (using PHP).

select name, max(score)
from SCORES
group by name
order by name

Is there any way to do this directly in SQL?

Answer 1

SELECT name, score
FROM SCORES
JOIN (SELECT distinct score score3
      FROM scores
      ORDER BY score DESC
      LIMIT 2, 1) x
ON score >= score3
ORDER by score DESC

FIDDLE

Answer 2

SELECT name,score
    FROM SCORES
    WHERE score in (
        SELECT distinct s.score
        FROM SCORES as s
        ORDER BY s.score desc
        LIMIT 3)
    )
    ORDER BY score

Answer 3

SELECT  Name, MAX(Score) Score
FROM    TableName a
WHERE   EXISTS
        (
            SELECT  1
            FROM    TableName b
            WHERE   a.Score = b.Score
            GROUP   BY Score
            ORDER   BY Score DESC
            LIMIT   3
        )
GROUP   BY Name
ORDER   BY Score DESC

• SQLFiddle Demo

OUTPUT based on the records given above

╔══════╦═══════╗
║ NAME ║ SCORE ║
╠══════╬═══════╣
║ Mark ║    29 ║
║ John ║    29 ║
║ Matt ║    29 ║
║ Luke ║    28 ║
║ Paul ║    27 ║
╚══════╩═══════╝

Answer 4

this will give the top three scores, regardless of ties.

SELECT score FROM mytable group by score ORDER BY score DESC LIMIT 3

now get the third score.

SELECT MIN(score') FROM (SELECTscoreFROMmytablegroup by score ORDER BYscore` DESC LIMIT 3) as top3

finally get everything equal or above the third score

SELECT * FROM mytable WHERE score' >= (SELECT MIN(score') FROM (SELECT score FROM mtyable group by score' ORDER BYscore` DESC LIMIT 3) as top3)