Convert MAX() KEEP (DENSE_RANK LAST ORDER BY TIMESTAMP ) OVER( PARTITION BY) to MySql

Question

I need to convert several views from Oracle to mySql. I managed to convert all but one:

CREATE or replace VIEW BPMS_POC_ACC_LATEST_ANSWERS_V (PROCESS_ID, QUESTION_ID, QUESTION_GRP, ANSWER, VERSION, TYPE, AUTHOR, TIMESTAMP, PKEY) AS
WITH t2 AS
(
SELECT BPMS_POC_ACC_ANSWER.*, MAX(TIMESTAMP) KEEP (DENSE_RANK LAST ORDER BY TIMESTAMP ) OVER( PARTITION BY QUESTION_GRP, QUESTION_ID , PROCESS_ID ) 
            AS highest
  FROM BPMS_POC_ACC_ANSWER

)
SELECT PROCESS_ID, QUESTION_ID, QUESTION_GRP, ANSWER, VERSION, TYPE, AUTHOR, TIMESTAMP, CONCAT(CONCAT(PROCESS_ID,QUESTION_GRP),QUESTION_ID) AS PKEY
  FROM t2
 WHERE highest = TIMESTAMP 

I believe the problem is in: MAX() KEEP (DENSE_RANK LAST ORDER BY TIMESTAMP ) OVER( PARTITION BY)

Does anybody know an easy way to convert it?

Answer 1

As far as concerns, this query gives you the latest record for each (QUESTION_GRP, QUESTION_ID , PROCESS_ID) tuple, ordered by TIMESTAMP.

You could simply use a correlated subquery for that:

CREATE or replace VIEW BPMS_POC_ACC_LATEST_ANSWERS_V 
    (PROCESS_ID, QUESTION_ID, QUESTION_GRP, ANSWER, VERSION, TYPE, AUTHOR, TIMESTAMP, PKEY) AS
SELECT 
    PROCESS_ID, 
    QUESTION_ID, 
    QUESTION_GRP, 
    ANSWER, 
    VERSION, 
    TYPE, 
    AUTHOR, 
    TIMESTAMP, 
    CONCAT(CONCAT(PROCESS_ID,QUESTION_GRP),QUESTION_ID) AS PKEY
FROM BPMS_POC_ACC_ANSWER a
WHERE a.TIMESTAMP = (
    SELECT MAX(a1.TIMESTAMP)
    FROM BPMS_POC_ACC_ANSWER a1
    WHERE 
        a1.QUESTION_GRP = a.QUESTION_GRP
        AND a1.QUESTION_ID = a.QUESTION_ID
        AND a1.PROCESS_ID = a.PROCESS_ID
)

The upside of this approach is that it will work on all versions of MySQL, even in versions 5.x, which do not support window functions.

In MySQL 8.0, you could also use RANK():

CREATE or replace VIEW BPMS_POC_ACC_LATEST_ANSWERS_V 
    (PROCESS_ID, QUESTION_ID, QUESTION_GRP, ANSWER, VERSION, TYPE, AUTHOR, TIMESTAMP, PKEY) AS
SELECT 
    PROCESS_ID, 
    QUESTION_ID, 
    QUESTION_GRP, 
    ANSWER, 
    VERSION, 
    TYPE, 
    AUTHOR, 
    TIMESTAMP, 
    CONCAT(CONCAT(PROCESS_ID,QUESTION_GRP),QUESTION_ID) AS PKEY
FROM (
    SELECT 
        a.*,
        RANK() OVER(PARTITION BY QUESTION_GRP, QUESTION_ID, PROCESS_ID ORDER BY TIMESTAMP DESC) rn
    FROM BPMS_POC_ACC_ANSWER a
) a
WHERE rn = 1