Somehow a table is having junk data, need to clean it up and generate a new table.
I think it should use case or some row_number over, tried a few, failed. Can you please help ?
original table:
Student Registration Course
John CS
John 2018
John 2017
Peter 2019 MATH
Mary 2016 MATH
The rule is, if we have duplicate records for a student, merge them together, for Registration, take max of year, so the result will be :
Student Registration Course
John 2018 CS
Peter 2019 MATH
Mary 2016 MATH