0

I have below table output from a query.

enter image description here

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. enter image description here

Thanks in advance.

MT0
  • 143,790
  • 11
  • 59
  • 117
Subhamoy Ghosh
  • 79
  • 9
  • Your question is really hard to read. Perhaps you could put sample data in the question as *text tables*. And provide a clear explanation of what you want to accomplish. – Gordon Linoff Aug 19 '21 at 15:59
  • @GordonLinoff you can consider first image as input and second one is output. And as you can see there are more records for same ref_period_value_code and same ffi_customer_id. I want for each ffi_customer_id and ref_period_value_code based single records as output. Based on reported_on column but reported_on also same for few records so next need to put filter on created_on column to get the output. I think this will help you. click on image will show you in bigger mode. – Subhamoy Ghosh Aug 19 '21 at 16:08

1 Answers1

0

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;
NewToGo
  • 83
  • 5
  • 1
    The query is not only in *high invalid* syntax, but also the function `RANK` is inappropriate if in case of ties you want to get only one row. In this case `ROW_NUMBER` should be used: `select * from ( select tab.*, row_number() over (partition by customer_id, period order by reported_on DESC, created_on DESC) as rn from tab) where rn = 1;` – Marmite Bomber Aug 19 '21 at 20:24
  • Assuming for given partition and ordering there is only one record rank can be used, otherwise row_number can be used to pick the record depending on functionality. – NewToGo Aug 19 '21 at 21:15