Query optimisation..

gistfile1.sql

SELECT c.contract_id, csc1.changed_status
  FROM contract c
  LEFT
  JOIN contract_status_change csc1
    ON csc1.contract_status_change_id =
        ( SELECT csc2.contract_status_change_id
            FROM contract_status_change csc2
           WHERE csc2.contract_id = c.contract_id
           ORDER
              BY csc2.date_changed DESC
           LIMIT 1
        )
 ;