CODEWITHSUNDEEP
sqlsql-serversql-server-2012
bbadger16
bbadger16

Reputation: 13

SQL distinct query over two tables

I would like to find the distinct reviews (by year) for each employee showing only the most recent review (by year)

Employee List                 Employee Reviews
Number | Employee Name        GUID | Number | Year
1234     John                 x5848  1234     2016
4526     Jim                  xd565  1234     2015
1123     Pam                  cr484  1123     2016

Result Needed:

Number | Name | GUID | Year
1234     John   x5848  2016
1123     Pam    cr484  2016

I can't figure out how to write a SQL query that would return me the above results. Anyone have any experience with a query like this or similar?

Upvotes: 1

Views: 58

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You can rank your records by giving row numbers to them:

select 
  guid, number, year, 
  row_number() over (partition by number order by year desc) as rn
from employee_reviews;

This gives the newest records (order by year desc) per employee (partition by number) row number 1. Hence:

select emp.number, emp.name, rev.guid, rev.year
from employee amp
join
(
  select 
    guid, number, year, 
    row_number() over (partition by number order by year desc) as rn
  from employee_reviews
) rev on rev.number = emp.number and rev.rn = 1;

Upvotes: 1

Related Questions