Oracle- How to get single row from multiple same type of rows

Question

I have below table output from a query.

and I want the final output as below image from the above output. Main goal is to figure out one one rows for each ffi_customer_id for each year based on reported_on and created_on date.

Thanks in advance.

Answer 1

This kind of problem can be solved with analytic functions. You can read more here https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174

For example you select the columns which you want in outer select and then in subquery rank over partition by ffi_customer_id, ref_period_value_code order by created_on and reported_on in descending order. Then select the records where rank is 1.

SELECT ffi_customer_id
     ,ref_period_value_code
     ,created_on
     ,reported_on
     ,financial_master_id
FROM (  SELECT your_table_name.*
              ,RANK() OVER(PARTITION BY ffi_customer_id, ref_period_value_code ORDER BY reported_on DESC, created_on DESC) AS "Rank"
         FROM (SELECT * FROM your_table_name) AS table2(ffi_customer_id, ref_period_value_code, created_on, reported_on, financial_master_id)) table22) t0 WHERE "Rank" = 1;