Uniques based on highest value on another column

[Anonymous]

Hello, so I have a dataset as a .csv file that has two columns, one for "professorID" , "Degree" and i'd like to get the uniques from professorID based on the highest value that he has on "Degree". I have uploaded an image with the example, in this case Doctor is a higher degree than Master for example. Any suggestions?

[akumar2301 · Accepted solution]

You need to have one mapping table to define Degree order e.g
Doctor:1
Master:2
Bechelore:3

Then join your data and these mapping. Then you taggregaterow to get the highest degree for each professional.

[Anonymous]

Based on MySql tag, you can use row_number window function and use FIELD() to give priority to various degrees and select 1st row. Refer below links. Hope this helps

http://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/

http://www.mysqltutorial.org/mysql-order-by/