How Can I Get Highest Degree For an Employee Using Following Tables?

Question

I have an Employee Table:

ID      Name  
1        Jhon
2        Mark

And an Education Table:

ID       Degree              Emp_ID
1         High School         1
2         Bachelor            1
3         Masters             1
4         Bachelor            1
5         High School         2
6         PHD                 2
7         Masters             2

Considering that another bachelor is added for Jhon after his master's, and a masters degree is added after the PHD for Mark, considering this concept I want to retrieve the highest degree for each employee as follows:

Name     Degree
jhon       Master
Mark       PHD

I am using PostgreSQL. Please kindly help. thanks in advance

Answer 1

select x.name, x.degree from (
select 
    em.name,
    ed.degree,
    row_number() over (partition by em.name order by
        case 
        when ed.degree = 'High School' then 1 
        when ed.degree = 'Bachelor' then 2 
        when ed.degree = 'Masters' then 3
        when ed.degree = 'PHD' then 4 
        else null 
        end
    desc) as degree_rank
from employee em
left join education ed
    on em.id = ed.emp_id 
) x
where x.degree_rank = 1