SELECT *
FROM (SELECT RANK() OVER(PARTITION BY vin ORDER BY change_starttime DESC) NUM,
A.vin,
A.change_time,
A.change_state
FROM t_service_change_log A WHERE A.change_starttime < sysdate - 1 / 24 / 6 order by a.key_id desc)
WHERE NUM = 1