We've an audit table that tracks of the changes of the different tables along with the column_names, user, timestamp, oldvalue and the newvalue based on the Invoicenumber. I need to retrieve latest audit data (last timestamp) across a specific set of tables and specific set of columns for a particular invoicenumber.

Now I need to get the last updated records for Invoice number 112 with changes made in (Table A & Column 1, 2) and changes made in (Table B & column 1,2).

I tried using rank but not sure how to expand it beyond one column.

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE:

SELECT *
FROM   (
  SELECT *
  FROM   table_name
  WHERE  invoice_number = 112
  AND    table_name IN ('A', 'B')
  AND    column_name IN (1, 2)
)
MATCH_RECOGNIZE (
  PARTITION BY /*invoice_number,*/ table_name, column_name
  ORDER     BY timestamp DESC
  ALL ROWS PER MATCH
  PATTERN (^ last_row)
  DEFINE  last_row AS 1 = 1
)

In earlier versions, you can use the ROW_NUMBER analytic function:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY /*invoice_number,*/ table_name, column_name
           ORDER     BY timestamp DESC
         ) AS rn
  FROM   table_name t
  WHERE  invoice_number = 112
  AND    table_name IN ('A', 'B')
  AND    column_name IN (1, 2)
)
WHERE  rn = 1;

Note: since you only have a single invoice_number you don't need to include it in the PARTITION BY clause; however, if you had multiple invoice_numbers then you would want to.

Which, for the sample data:

CREATE TABLE table_name (Invoice_Number, Table_Name, Column_Name, Timestamp, "USER", Old_Value, New_Value) AS
SELECT 112, 'A', 1, TIMESTAMP '2021-10-10 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 112, 'B', 1, TIMESTAMP '2021-10-11 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 112, 'A', 2, TIMESTAMP '2021-10-11 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 114, 'B', 1, TIMESTAMP '2021-10-12 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 112, 'A', 2, TIMESTAMP '2021-10-13 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 122, 'B', 2, TIMESTAMP '2021-10-13 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 122, 'A', 5, TIMESTAMP '2021-10-13 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 112, 'A', 2, TIMESTAMP '2021-10-15 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 114, 'B', 3, TIMESTAMP '2021-10-16 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 112, 'B', 2, TIMESTAMP '2021-10-18 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 112, 'A', 1, TIMESTAMP '2021-10-10 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 142, 'B', 1, TIMESTAMP '2021-10-11 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 102, 'A', 2, TIMESTAMP '2021-10-11 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 184, 'B', 1, TIMESTAMP '2021-10-12 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 142, 'D', 2, TIMESTAMP '2021-10-13 12:00:00.111111', 'User1', 7, 6 FROM DUAL UNION ALL
SELECT 118, 'C', 2, TIMESTAMP '2021-10-18 12:00:00.111111', 'User1', 7, 6 FROM DUAL

Outputs:

TABLE_NAME	COLUMN_NAME	TIMESTAMP	INVOICE_NUMBER	USER	OLD_VALUE	NEW_VALUE
A	1	10-OCT-21 12.00.00.111111000	112	User1	7	6
A	2	15-OCT-21 12.00.00.111111000	112	User1	7	6
B	1	11-OCT-21 12.00.00.111111000	112	User1	7	6
B	2	18-OCT-21 12.00.00.111111000	112	User1	7	6

db<>fiddle here