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.
- 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– Gordon Linoff2021-08-19 15:59:04 +00:00Commented Aug 19, 2021 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– Subhamoy Ghosh2021-08-19 16:08:48 +00:00Commented Aug 19, 2021 at 16:08
Add a comment |
1 Answer
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; 
2 Comments
Marmite Bomber
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;
NewToGo
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.