A little late to the party, but this is a variation on the cte that was posted earlier by J Livingston SQL. I like the cte because it is so clean. Give it a shot.

;with cte as

(

Select iPhone_id, location_id, date_assigned,

ROW_NUMBER() over(partition by iphone_id order by date_assigned desc) RowNum

from location_history

)

select iP.iphone_id, iP.seriel_number, c.date_assigned, l.Emp_name, l.Department

from iPhones iP

join cte c on c.iPhone_id = iP.iphone_id and c.RowNum = 1

join locations l on l.location_id = c.location_id