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

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

Answers 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
    

Related Questions