Search code examples
mysqlsqlcountmaxlimit

I need one string with max value


I have such quire:

SELECT s_name, count(Mark)
FROM specialty
join interns_specialty on specialty.specialty_id = interns_specialty.specialty_id
join practice_result on practice_result.Intern_id = interns_specialty.intern_id
where Mark=5
group by specialty.specialty_id
ORDER by count(Mark) DESC;

That is what i get from my quire, i need to take the row with the max count of excellent marks, if there is two or more such rows, i must to get it all I need to get one string with the name of specialty which has more excellent marks than others. But i cant understand how to do this. I tried to use "Limit", but if i have two or more specialties - it will be work incorrect. I couldnt use MAX with another aggregate functions, so i need an advice.


Solution

  • I think you want rank():

    SELECT m.*
    FROM (SELECT s.s_name, COUNT(*),
                 RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
          FROM specialty s JOIN
               interns_specialty ins
               ON s.specialty_id = ins.specialty_id JOIN
               practice_result pr
               ON pr.Intern_id = ins.intern_id
          WHERE Mark = 5
          GROUP BY s.specialty_id
         ) m
    WHERE seqnum = 1;