0

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?

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by tesuji
0

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

Edited by tesuji: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.