Member Avatar for 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?

  • 2 Contributors
  • 1 Reply
  • 110 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by tesuji

Dani AI

Generated

As described, the goal is to collapse rows that share Student_ID and Class and keep only the most recent attempt (highest Sequence). On SQL Server the most readable and reliable way is a window function that numbers attempts per student/class and then selects the first one.

WITH Latest AS (
  SELECT
    Sequence, Student_ID, Class, Grade,
    ROW_NUMBER() OVER (PARTITION BY Student_ID, Class ORDER BY Sequence DESC) AS rn
  FROM dbo.YourTable
)
SELECT Sequence, Student_ID, Class, Grade
FROM Latest
WHERE rn = 1;

Notes and cautions: if Sequence is not a true chronological key (rows could be inserted out of order) use an exam timestamp or add it as a tiebreaker in the ORDER BY. If you want to keep ties (multiple rows with the same highest Sequence) use RANK() instead of ROW_NUMBER() so all tied rows are returned. As suggested, storing an exam date provides clearer semantics and makes ordering explicit.

Performance tips: on large tables create a covering nonclustered index on (Student_ID, Class, Sequence DESC) with Grade as an INCLUDE column so the engine can seek and avoid lookups. If this query runs very frequently, consider maintaining a small "current_grades" table or a boolean IsCurrent flag updated at write time — that shifts cost to inserts/updates but makes reads trivial.

References: official docs for ROW_NUMBER and index creation are useful for syntax and behavior details: ROW_NUMBER documentation and CREATE INDEX.

Member Avatar for 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

Edited by tesuji because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.