How to get latest child table id based on condition

I am writing one query to get results based on parent table records and latest child table id based on condition

Sample Data:

*Department*

Id  Name 
-----------
1   Dept1
2   Dept2
3   Dept3
4   Dept4

*Employee*

Id DeptId  quality   VerifiedOn
----------------------------------
1    1      Ok        '2014-08-01'
2    2      Ok        '2014-09-01'
3    2      Best      '2014-08-01'
4    2      Good      '2014-08-07'
5    4      Good      '2014-10-08'
6    4      Ok        '2014-10-01'
7    4      Good      '2014-09-01'

Requirement

I need to get all department details along with latest good quality employee Id.

Good Quality Employee logic is :

• Take Latest employeeId of Quality is ‘Best’
• If not Best quality is there, take latest of employeeId
• If no employee available for the department return 0 or NULL

[top 1 order by VerifiedOn desc]

Need to create viwew for this

Expected Result:

DeptId DeptName EmpiId
-----------------------
  1     Dept1     1
  2     Dept2     3
  3     Dept3     0
  4     Dept4     5

You need to sign in to view this answers