u/OddCardiologist2981

SELECT 
            H1.created_at , H1.event_type, 
            CASE 
                WHEN H1.event_type = 9 THEN H1.animal_id
                ELSE "No animal assigned"
            END as animal_id,
            H1.rank_assigned_by ,H1.supervisor_id as CURR_SUPERVISOR, 
            H2.supervisor_id as PREV_SUPERVISOR
            ,H1.rescue_point_id , H1.emp_rank as CURR_RANK , H2.emp_rank as PREV_RANK  , 
            H1.salary as CURR_SALARY , H2.salary as PREV_SALARY ,H1.reason
            from Employee_history  H1 left join  (
                SELECT * from Employee_history  E1 where E1.created_at < Employee_history.created_at 
                order by E1.created_at
                limit 1
            ) as H2 on H1.emp_id = H2.emp_id;