[SQL] 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]

Expected Result:

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

Continue reading...