Dynamic ordering ambiguous column issue

One way would be to do the following

;With Employees As
(Select Distinct e.Emp_ID as Emp_ID
         
        ,p.First_Name
        ,p.Last_Name
FROM Employee e
LEFT OUTER JOIN Team t ON e.team_id = t.team_Id AND e.Emp_id = t.Emp_Id
LEFT OUTER JOIN AccrualType at ON e.Accrual_Type = at.Type
LEFT OUTER JOIN ClosingProcess cp ON e.Emp_id = cp.Emp_ID 
where Last_Name like 's%')

SELECT RowNumber() Over (Order By Emp_ID) As RowNum
        ,Emp_ID
        ,First_Name
        ,Last_Name
FROM Employees
order by Emp_ID 

Tom