I have this audit table

And I need as a result, something like this:

So I can know which column changed from the last time.

Something like this is working, but I think may be a way to optimize it? or at least generate a "more easy to look" query? (i need the information for almost 20 columns, not only 3)

SELECT 
    ta.audit_date,
    ta.audit_user,
    CASE
        WHEN ta.audit_operation = 'I' THEN 'Insert'
        WHEN ta.audit_operation = 'U' THEN 'Update'
    END AS action,
    CASE WHEN ta.column1 <> (SELECT column1
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column1,
    CASE WHEN ta.column2 <> (SELECT column2
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column2,
    CASE WHEN ta.column3 <> (SELECT column3
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column3
FROM
    audit_table ta
WHERE
    ta.id = 9207
ORDER BY
    audit_date DESC

Thank you!

CodePudding user response：

I think you can just use the LAG() analytic function here. If I understand correctly:

SELECT *, CASE WHEN text != LAG(text) OVER (ORDER BY date) THEN 'x' END AS text_label,
          CASE WHEN text2 != LAG(text) OVER (ORDER BY date) THEN 'x' END AS text2_label
FROM yourTable
ORDER BY date;