现有一组流程跟踪数据，请用SQL取出每一个流程中的最新创建的记录。数据结构如下，假设只有四个字段，依次为ID(主键)，PROCESS_INS_ID(流程实例ID)，CREATE_TIME（创建时间）,DEAL_UID（处理人）
[{'1','P_1','2019-07-03 18:00:00','ZHANG'},{'2','P_1','2019-07-03 18:01:00','ZHANG'},
{'3','P_1','2019-07-03 18:02:00','ZHANG'},{'4','P_2','2019-07-03 18:00:00','ZHANG'},
{'5','P_2','2019-07-03 18:01:00','ZHANG'},{'6','P_3','2019-07-03 18:00:00','ZHANG'},
{'7','P_3','2019-07-03 18:01:00','ZHANG'},{'8','P_3','2019-07-03 18:02:00','ZHANG'}]
要求查询出的结果为
{'3','P_1','2019-07-03 18:02:00','ZHANG'} p_1中 创建时间最新的
{'5','P_2','2019-07-03 18:01:00','ZHANG'} p_2中 创建时间最新的
{'8','P_3','2019-07-03 18:02:00','ZHANG'} p_3中 创建时间最新的
请选择基于Oracle或者mysql数据库给出sql语句。
MYSQL 实现方式
SELECT t.*
FROM (
SELECT PROCESS_INS_ID, MAX(CREATE_TIME) as CREATE_TIME
FROM test
GROUP BY PROCESS_INS_ID
) r
INNER JOIN test t
ON t.PROCESS_INS_ID = r.PROCESS_INS_ID AND t.CREATE_TIME = r.CREATE_TIME
GROUP BY t.PROCESS_INS_ID;