SELECT * FROM `table`
ORDER BY `status` ASC,
CASE WHEN `status` = 1 THEN `start_date` END DESC,
CASE WHEN `status` = 3 THEN `end_date` END DESC;