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

  • I tried to generate a copy of Education table and add a sortby column to it and assign each degree a unique number then it worked, but i dont want to do this using this method which is copying of table. Aug 8, 2016 at 4:33
  • @peach This was awesome it worked, Thank you very much Aug 8, 2016 at 4:34

1 Answer 1

select x.name, x.degree from (
    row_number() over (partition by em.name order by
        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 
    desc) as degree_rank
from employee em
left join education ed
    on em.id = ed.emp_id 
) x
where x.degree_rank = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.