How to select top 3 highscore in mysql

Question

I have a 'users' table as follow.

id  name    gender  highscore
1   Smith   male    5
2   David   male    8   
3   Ken     female  15
4   Trump   male    8
5   Jone    male    4
6   Nicoel  male    6
7   Ben     male    32
8   Micale  male    5
9   Stancu  male    6

I want to select users information that exists within top 3 highest score. Scores may be duplicate but the result only depends on top 3 highest score, I mean that the result should be

id  name    gender  highscore
7   Ben     male    32
3   Ken     female  15  
2   David   male    8
4   Trump   male    8

I am trying following query but it is only get highest users.

select * from `users` where `highscore`=(select max(`highscore`) from users);

Answer 1

Finally, I got answer from this. I tested and confirmed. Here is my output and query.

SELECT name, highscore 
FROM users 
WHERE FIND_IN_SET(highscore, (
   SELECT SUBSTRING_INDEX(GROUP_CONCAT(
     DISTINCT highscore ORDER BY highscore DESC),',',3) 
   FROM users)) > 0 
ORDER BY highscore desc 

Answer 2

You can also try the following query.

SELECT name, highscore 
FROM users JOIN (
  SELECT distinct highscore score3 
  FROM users 
  ORDER BY highscore DESC LIMIT 2, 1) x ON highscore >= score3 
ORDER by highscore DESC ;

Answer 3

SQL-transact syntax:

select top 3 * from users order by highscore desc

mySQL syntax :

select * from `users` order by `highscore` desc limit 3

Oracle syntax :

select * from users where ROWNUM <= 3 order by highscore desc

Answer 4

Try order by with limit :

select * from `users` order by `highscore` desc limit 3