CODEWITHSUNDEEP
mysqlsqldatabase
SooIn Nam
SooIn Nam

Reputation: 3171

How do i get top 5 score in mysql

I am trying to get top 5 score from student table. table looks like that.

   table: student

   id name   score

   1    a     100
   2    b     100
   3    c     90
   4    d     87
   5    e     85
   6    f     88
   7    g     83
   9    h     92

if i do 

   select name,score from student order by score desc limit 5

   a    100
   b    100
   h     92
   c     90
   f     88

however, I want to see this result 

   a    100
   b    100
   h     92
   c     90
   f     88
   d     87

I am trying to figure out duplicated score as count one Any solution? Thanks in advance.

Upvotes: 2

Views: 4151

Answers (4)

Bhaskar Bhatt
Bhaskar Bhatt

Reputation: 1467

here is sql fiddle

  SELECT s.id, s.name,s.score FROM tbl_student AS s INNER JOIN (SELECT * FROM tbl_student ORDER BY score DESC LIMIT 0,5) AS s2 ON s2.id=s.id order by s.score desc

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT s.*
FROM student AS s
  JOIN
    ( SELECT DISTINCT score
      FROM student
      ORDER BY score DESC
          LIMIT 5
    ) AS lim
    ON s.score = lim.score 
ORDER BY s.score DESC ;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Here is one way:

select s.*
from student s
where s.score >= (select score
                  from (select distinct score from student order by score desc limit 5) s
                  order by score
                  limit 1
                 )

This gets the scores in descending order in the inner most subquery. It limits this to five distinct scores. Then, it find the smallest value, and returns all rows that have this score or greater.

Upvotes: 2

John Conde
John Conde

Reputation: 219804

Try using GROUP BY:

SELECT 
    score
    COUNT(*) AS score_count
FROM 
    student 
GROUP BY
    score
ORDER BY
    score desc 
LIMIT 5

Upvotes: 1

Related Questions