I have a employee table
empid empname status
1 raj active
2 ravi active
3 ramu active
4 dan active
5 sam inactive
I have another table called facilities
empid timestamp
1 2014-12-28
1 2015-05-05
1 2015-06-05
2 2015-05-03
2 2015-06-04
3 2015-02-01
I want my result like
empid empname status lastusedts
1 raj active 2015-06-05
2 ravi active 2015-06-04
3 ramu active 2015-02-01
4 dan active null
So i have to join my employee table and facilities table and find when the employee has last used the facilities by getting the max of time stamp and for employees who did not use it the timestamp value should be null and only active employees are to be fetched. Please help me in writing this query in db2
Do a
LEFT JOIN
with aGROUP BY
to find theMAX
(timestamp):Alternatively, a correlated sub-select for the max-timestamp: