sql - 在一个查询中比较 Postgres 中的两个值

Question

到目前为止，这是我的查询:

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

这是输出:

现在我要做的是只取回一个类(class)最频繁的成绩。因此，我需要将成绩为 B 的软件开发 I 裁掉，因为 A 是类(class)中出现频率最高的成绩。但我不知道如何比较计数值。任何帮助都会很棒。

Answer 1

如果你不需要计数(根据你的评论)，你可以尝试这样的事情

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;

下面的查询也将得到计数

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;