topic Re: Uniques based on highest value on another column in Talend Studio

Uniques based on highest value on another column

Anonymous — Sat, 16 Nov 2024 04:04:46 GMT

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?

Re: Uniques based on highest value on another column

akumar2301 — Tue, 19 Nov 2019 17:59:16 GMT

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.

Re: Uniques based on highest value on another column

Anonymous — Tue, 19 Nov 2019 19:24:56 GMT

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/