CODEWITHSUNDEEP
mysql
Pablo H
Pablo H

Reputation: 157

How do I select only the maximum value?

My query looks like this:

select e.cid, e.section,
sum(if(e.grade =  'd' or e.grade = 'f', 1, 0)) as count_fails,
sum(if(e.grade = 'a' or e.grade = 'b' or e.grade = 'c' , 1, 0)) as count_pass
from homework3.enrollment e
group by e.cid, e.section 
having max((count_fails/(count_fails + count_pass))*100);

What I am trying to do is return only the tuple which has the greatest percentage of failing. The above query is returning all tuples which have a value greater than 0 for the having condition.

I don't want to select the failure percentage because this is a subquery and I cannot have 3 operands.

Just to be absolutely clear, if this is my table, I want to only return the (336,1) tuple but without the percent_fail column.

enter image description here

Thanks!

Upvotes: 0

Views: 49

Answers (1)

forpas
forpas

Reputation: 164139

Add an ORDER BY clause with LIMIT:

select e.cid, e.section
from homework3.enrollment e
group by e.cid, e.section 
order by sum(e.grade in ('d', 'f')) / count(*) desc limit 1

For MySql 8.0+ use rank() window function:

select t.cid, t.section
from (
  select cid, section,
    rank() over(order by sum(grade in ('d', 'f')) / count(*) desc) rn
  from homework3.enrollment
  group by cid, section
) t
where t.rn = 1

Upvotes: 1

Related Questions