I need to select duplicates from multiple columns and only show the most recent

[uwmbjn]

I have table that has students classes and grades. They are allowed to fail a test twice but if they fail twice they have to retake the couse in which they have two more tries.

I would like to show only the most recent grade. Here is the table structure

Sequence | Student_ID | Class | Grade |
1 1 Math F
2 1 Math F
3 1 Math P
4 2 Math P
5 3 Eng F
6 1 Eng P

I would like the results to just show

Sequence | Student_ID | Class | Grade |
1 1 Math P
4 2 Math P
5 3 Eng F
6 1 Eng P

So I want to look for duplicates across Student ID and Class and then print out only the most recent (Sequence descending).

I am at a loss. Any ideas?

[tesuji]

Hi

your task can easily be solved by means of a sub-select, in principle:

select * from yourtable 
  where sequence in (select max(sequence) from yourtable group by ...) order by ...;

Now your personal task is to figure out how group-by and order-by clauses must look like to meet your requirements. It's easy, try it with studid, class and also sequence for the latter clause.

There are other ways to solve your problem, for example with OLAP methods. Here I wrote something about using window function row_number() with partitioning, but your problem is much easier than his one.

Shall that be an assignment, I would suggest to get rid of this unnatural column "Sequence", obviously also auto-incremented, by introducing the date the exam took place. Then you would also have a perfect primary key (studid, class, date).

-- tesu