CODEWITHSUNDEEP
mysqlsqlselectlimit
Z.V
Z.V

Reputation: 1461

Select top n amount (by considering some record have same amount)

Having table Student (column: StudentID, ClassID). Trying to find out top 3 class with most student. If there are same amount of student, must list out also. been trying the following:

SELECT 
DISTINCT ClassID, COUNT(StudentID)
FROM
Student
GROUP BY ClassID
ORDER By COUNT(StudentID)
DESC Limit 3

but this doesnt list out the same amount, in fact, if there are 5 numbers: 40,20,20,10,5 it will list out 40,20,20. But I want to list out the top 3 with result 40,20,20,10.

Was wondering whether there is something like ranking in MySQL that can be used to resolve this..

Upvotes: 2

Views: 81

Answers (1)

xdazz
xdazz

Reputation: 160833

You could join with the top 3 distinct count values:

SELECT A.*
FROM (
    SELECT COUNT(StudentID) AS student_count, ClassID
    FROM Student
    GROUP BY ClassID
) A INNER JOIN (
    SELECT  DISTINCT COUNT(StudentID) AS student_count
    FROM Student
    GROUP BY ClassID
    ORDER BY student_count DESC
    LIMIT 3
) B ON A.student_count = B.student_count

THE SQL FIDDLE.

Upvotes: 3

Related Questions