Search code examples
sqldatabaseoracle-databasesql-insertcreate-table

How to get distinct records based on flag or most recent update timestamp?

I am trying to get records based on following conditions.

Table : store

Schema:

create table store (product varchar(50),product_id number,product_type varchar(10),product_flag char(1),product_upd_dt Date);
insert into store(product,product_id,product_type,product_flag,product_upd_dt)values('Apple',123,'GALA','Y','16-MAR-20 06.49.05');
insert into store(product,product_id,product_type,product_flag,product_upd_dt)values('Apple',123,'GALA','N','16-MAR-20 06.49.05');
insert into store(product,product_id,product_type,product_flag,product_upd_dt)values('Apple',134,'JAZZ','N','16-MAR-20 06.49.05');
insert into store(product,product_id,product_type,product_flag,product_upd_dt)values('Apple',134,'JAZZ','N','14-MAR-20 06.49.05');
insert into store(product,product_id,product_type,product_flag,product_upd_dt)values('Apple',134,'JAZZ','N','15-MAR-20 06.49.05');
insert into store(product,product_id,product_type,product_flag,product_upd_dt)values('Apple',13,'ENVY','Y','15-MAR-20 06.49.05');
commit;

1)Out of this i want to get product_id based on product_flag='Y'

2)If the product_flag is 'N' then get the most recent updated product_id.

3)One product can mapped to same product_id, but different product_types.

4)Same row can be repeated but difference in product_upd_dt

Here is an example.

Input Image

enter image description here

Here is the expected Output-

Output Image

enter image description here

Thanks in Advance.

Solution

  • Ah, 1-4 represent directions, not separate questions ... sorry, I initially misunderstood the problem.

    How about this?

    SQL> select product,
  2    product_id,
  3    product_type,
  4    max(product_flag) product_Flag,
  5    max(product_upd_dt) product_upd_dt
  6  from store
  7  where product_id in (select product_id
  8                       from store
  9                       where product_flag = 'Y'
 10                      )
 11  group by product, product_id, product_type;

PRODUCT    PRODUCT_ID PRODUCT_TY P PRODUCT_UPD_DT
---------- ---------- ---------- - ------------------
Apple             134 ENVY       Y 15-mar-20 06.49.05
Apple             123 GALA       Y 16-mar-20 06.49.05
Apple             134 JAZZ       N 16-mar-20 06.49.05

SQL>