My database has some entries for which a categorical field has been updated and the old value wasn't removed. I want to keep only the entry with the "highest" categorical value, in an order which I define.
Input:
id | degree
=============
1 | Diploma
1 | Bachelors
1 | Masters
2 | Diploma
2 | Bachelors
Output (desired):
id | degree
=============
1 | Masters
2 | Bachelors
I tried using CASE with the "highest" value last in an attempt to overwrite the previous values, but it does not work as intended.
SELECT
DISTINCT id,
(CASE
WHEN id = 'Diploma' THEN 'Diploma'
WHEN id = 'Bachelors' THEN 'Bachelors'
WHEN id = 'Masters' THEN 'Masters'
END) as degree
FROM
academic_record
Is there a method for this operation?