Selecting a Max Value based on another variable in SQL

Question

I am trying to create an accurate count of student enrollment. There is an individual record for every course registration and any change to that particular course registration results in another record with an incremental sequence number. My table looks something like this:

ID    Course Number   Sequence Number
1     B101            1
1     B101            2
1     B101            3
1     C201            1
1     C201            2
2     E215            1
2     J320            1
2     J320            2

I would like to select the max value of sequence number such that every course registration is retained. This would mean that ID 1 would have 2 records. One would be B101 with sequence number=3 and another record for C201 with sequence number=2.

Answer 1

SELECT id, course_number, MAX(sequence_number) FROM table GROUP BY id, course_number;

Answer 2

You should first group all items on id then group them on Course_Number and show only the maximum value of Sequence_Number .

select id,Course_Number,max(Sequence_Number) from TblName group by id,Course_Number