How to filter result set based on unique column and highest volume in separate column?

Question

Title may be confusing. Not sure exactly how to put my issue into words. Here is the SQL statement I am working with:

SELECT * FROM (
   select * from course_student
   order by cnum ASC,
            year desc,
            case semester
               when 'Spring' then 1
               when 'Summer' then 2
               when 'Fall' then 3
            end DESC
   ) AS example
WHERE example.sid = 1
AND example.grade != 'I';

Here is the result from that query:

SID  CNUM       GRADE  SEMESTER  YEAR
1    "CPS441"   "A"   "Fall"     2013
1    "CPS441"   "D"   "Spring"   2012
1    "CPS442"   "B"   "Summer"   2013
1    "CPS445"   "A"   "Spring"   2013

Essentially I am grabbing every class that a particular student has taken. At this point I want to trim every duplicate result that follows the first occurrence (since the top result will have been the most recent attempt at the class). So in this case, I would want the second CPS441 class with a grade of 'D' to be filtered out of the result set and be left with the following:

SID  CNUM       GRADE  SEMESTER  YEAR
1    "CPS441"   "A"   "Fall"     2013
1    "CPS442"   "B"   "Summer"   2013
1    "CPS445"   "A"   "Spring"   2013

Another example would be if I started out with this:

SID  CNUM       GRADE  SEMESTER  YEAR
1    "CPS441"   "A"   "Fall"     2013
1    "CPS441"   "D"   "Spring"   2012
1    "CPS442"   "B"   "Summer"   2013
1    "CPS442"   "C"   "Spring"   2013
1    "CPS445"   "A"   "Spring"   2013

I would want to end up with this:

SID  CNUM       GRADE  SEMESTER  YEAR
1    "CPS441"   "A"   "Fall"     2013
1    "CPS442"   "B"   "Summer"   2013
1    "CPS445"   "A"   "Spring"   2013

score 0 · Answer 1 · answered Mar 29 '21 at 00:40

You seem to want row_number():

select * 
from (select cs.*,
             row_number() over (partition by sid, cnum
                                order by year desc,
                                         case semester when 'Spring' then 1 when 'Summer' then 2 when 'Fall' then 3 end desc
                               ) as seqnum 
      from course_student
      where grade <> 'I' and sid = 1
     ) cs
where seqnum = 1;

Erwin Brandstetter · Answer 2 · 2021-03-29T01:24:23.423

A textbook case for DISTINCT ON:

SELECT DISTINCT ON (cnum) *
FROM   course_student
ORDER  BY cnum
        , year DESC
        , CASE semester
            WHEN 'Fall'   THEN 1
            WHEN 'Summer' THEN 2
            WHEN 'Spring' THEN 3
          END
WHERE  sid = 1
AND    grade <> 'I';

See:

Select first row in each GROUP BY group?

One point is unclear: If the most recent event does not have sid = 1 AND grade <> 'I' would you rather drop the student from the result completely? Or just remove such rows and take the first valid row for the same student (like my solution does)?

Either way, students without valid rows are not in the result.

How to filter result set based on unique column and highest volume in separate column?

2 Answers2