Comparing two values in Postgres in one query

Question

So here is my query so far:

Select 
    class.title, studentClass.grade, count(studentClass.grade) 
from 
    classOffering 
inner join 
    studentClass on classOffering.classID = studentClass.classID
inner join 
    class on classOffering.classID = class.classID
group by 
    class.title, studentClass.grade
order by 
    count(studentClass.grade) desc

And here is the output:

Now what I am trying to do is only get back the most frequent grade for a class. So I need to cut out software development I with the grade of a B, because A is the most frequent grade in the class. But I do not know how to compare the count values. Any help would be wonderful.

Answer 1

If you dont need the count (based on your comments) you could try something like this

select a.title,a.grade from 
(Select class.title, studentClass.grade, 
row_number() over (partition by class.title order by studentClass.grade) as rn 
from classOffering inner join studentClass on classOffering.classID = studentClass.classID
inner join class on classOffering.classID = class.classID
group by class.title, studentClass.grade)a
where a.rn=1;

The below query will also get the count

select a.title,a.grade,a.gradeCount from 
    (Select class.title, studentClass.grade,
     count(studentClass.grade) over (partition by class.title) as gradeCount 
    row_number() over (partition by class.title order by studentClass.grade) as rn 
    from classOffering inner join studentClass on classOffering.classID = studentClass.classID
    inner join class on classOffering.classID = class.classID
    group by class.title, studentClass.grade)a
    where a.rn=1;