How do i get top 5 score in mysql

Question

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

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

What do you mean by "duplicated score"? Why are you excluding e and g from your desired result? — Melanie, Feb 20 '13 at 16:25
If you are trying to find the rankings rather than a straight count, this post has some interesting techniques for achieving that: http://stackoverflow.com/questions/3333665/mysql-rank-function — Tim Radcliffe, Feb 20 '13 at 16:28
I think you mean to say "I cannot filter out the duplicated score." in which case John Conde's answer is for you. — Kzqai, Feb 20 '13 at 16:30
Are you trying to count the number of students that scored 100? or the count of students that scored each grade? — Mike, Feb 20 '13 at 16:30

score 3 · Accepted Answer · answered Feb 20 '13 at 16:35

3

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 ;

answered Feb 20 '13 at 16:35

ypercubeᵀᴹ

113,259
19
174
235

i don't knew joins can be used like this also, Thanks – sandeepKumar Feb 20 '13 at 16:52

score 2 · Answer 2 · answered Feb 20 '13 at 16:34

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.

score 1 · Answer 3 · answered Feb 20 '13 at 16:23

1

Try using GROUP BY:

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

answered Feb 20 '13 at 16:23

John Conde

217,595
99
455
496

1

This returns five rows. The example results have six rows. – Gordon Linoff Feb 20 '13 at 16:32
Then change the LIMIT to 6 – John Conde Feb 20 '13 at 16:33
how do he can know if he has more number of records same???...u are saying limit it to 6 ? – sandeepKumar Feb 20 '13 at 16:40
They can change it to whatever they want. If they want six, they can change it to six. If they want more they can change it accordingly. It's basic SQL. – John Conde Feb 20 '13 at 16:43
1

he want to say that same(duplicate) records count as one row, so my argument was , one won't be knowing how much rows will be having same records – sandeepKumar Feb 20 '13 at 16:46

score 0 · Answer 4 · edited Feb 21 '13 at 19:36

0

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

edited Feb 21 '13 at 19:36

demongolem

9,474
36
90
105

answered Feb 21 '13 at 19:17

Bhaskar Bhatt

1,399
13
19

How do i get top 5 score in mysql

4 Answers4