Get the last value of a grouped result based on date-CodePudding

I am working with oracle SQL developer and I have an amendment table like the one below. This table tacks the amendments in the Quantity and Price field so, could have many times each contract, and field.

What I want is to have only the last value for each field of each contract according to the date of change. So if I have in a contract 10 amendments 5 for Quantity and 5 for Price I would like to keep only the last one that happened for price and the last one that happened for Quantity.

I tried the following code according to another post that I found but it doesn't work.

SELECT CONTRACT,
       FIELD,
       CASE 
       WHEN FIELD='PRICE' THEN
       MAX(NEW_VALUE ) KEEP ( DENSE_RANK LAST ORDER BY DATE_OF_CHANGE)
         END AS LAST_NEW_PRICE,
       CASE 
       WHEN FIELD='PRICE' THEN
       MAX(OLD_VALUE ) KEEP ( DENSE_RANK LAST ORDER BY DATE_OF_CHANGE )
        END AS LAST_OLD_PRICE,  
        CASE
       WHEN FIELD='QUANTITY' THEN
       MAX(NEW_VALUE ) KEEP ( DENSE_RANK LAST ORDER BY DATE_OF_CHANGE )
        END AS LAST_NEW_QUANTITY,
       CASE
       WHEN FIELD='QUANTITY' THEN
       MAX(OLD_VALUE ) KEEP ( DENSE_RANK LAST ORDER BY DATE_OF_CHANGE )
        END AS LAST_OLD_QUANTITY,
         DATE_OF_CHANGE
FROM dim_amendments 
GROUP BY CONTRACT;

So the expected result of the previous table I want to be like this

CodePudding user response：

SELECT CONTRACT,
       FIELD,
       MAX(NEW_VALUE ) KEEP ( DENSE_RANK LAST ORDER BY FIELD, DATE_OF_CHANGE)   LAST_NEW_PRICE,
       MAX(OLD_VALUE ) KEEP ( DENSE_RANK LAST ORDER BY FIELD, DATE_OF_CHANGE )  LAST_OLD_PRICE,
       MAX(DATE_OF_CHANGE ) KEEP ( DENSE_RANK LAST ORDER BY FIELD, DATE_OF_CHANGE )  LAST_DATE_OF_CHANGE
FROM dim_amendments 
GROUP BY CONTRACT,FIELD;

It worked like this actually !

CodePudding user response：

SELECT
    contract, field, old_value, new_value, date_of_change
FROM
    (
        select *, row_number() over (partition by contract, field order by date_of_change desc ) n 
        from dim_amendments
    )
WHERE 
    n = 1