Forum Stats

  • 3,768,181 Users
  • 2,252,755 Discussions
  • 7,874,485 Comments

Discussions

duplicate data differentiation

User_7PZDE
User_7PZDE Member Posts: 282 Blue Ribbon

Hi Team

I have data duplicate data differentiation like


create table supp_dept (

supp_id number,

part_id varchar2(100),

status varchar2(10),

date1 date,

comments varchar2(100),

rec_id number,

);

insert into supp_dept values(178845,'Horizon_234','risk','12-12-21',test-21',0);


insert into supp_dept values(178845,'Horizon_234','risk','13-12-21',test-22',1);


insert into supp_dept values(178845,'Horizon_234','risk','14-12-21',test-23',2);


insert into supp_dept values(178845,'GE_2342','risk','12-12-20',test-22',3);

I need to display new column flag old and new

old means if any data is old and duplicate previous records are to show old and

new is if it is unique and not duplicate need to display as new

here supp_id,part_id is primary key

rec_id is sequence generation for every supp_id,part_id

Let me know if need any further information

Tagged:

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy
    edited Jun 22, 2021 8:37AM

    I think the below is what you need.

    with tb as (

    select

    supp_id,

    part_id,

    status ,

    date1 ,

    comments ,

    rec_id,

    max(dte1) over (partition by supp_id, part_id) latest_date

    )

    select

    supp_id,

    part_id,

    status ,

    date1 ,

    comments ,

    rec_id,

    case

    when date1=latest_date then 'New'

    else 'Old'

    end Flag

    from tb

    ;

    Still, why doesn't your table have a primary key? What would that primary key be? I suppose it should be ( supp_id, part_id, date1).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    Hi, @User_7PZDE

    Don't try to insert VARCHAR2s (like '12-12-21') into DATE columns (like date1). Use TO_DATE or DATE literals instead.

    old means if any data is old and duplicate previous records are to show old and

    new is if it is unique and not duplicate need to display as new

    What is a "record"? If it's the same as a row, then why not say "row"?

    All rows in this sample data have the same values for supp_id and status, so however you define "previous" won't three of those four rows be flagged 'Old', since the data in those columns is duplicated on a previous row?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond

    Assuming old/new is column date1 based:

    select  s.*,
            case row_number() over(partition by supp_id,part_id order by date1 desc)
              when 1 then 'New'
              else 'Old'
            end flag
      from  supp_dept s
      order by supp_id,
               part_id,
               date1
    /
    
       SUPP_ID PART_ID     STATUS     DATE1    COMMENTS       REC_ID FLA
    ---------- ----------- ---------- -------- ---------- ---------- ---
        178845 GE_2342     risk       12-12-20 test-22             3 New
        178845 Horizon_234 risk       12-12-21 test-21             0 Old
        178845 Horizon_234 risk       13-12-21 test-22             1 Old
        178845 Horizon_234 risk       14-12-21 test-23             2 New
    
    
    SQL>
    

    SY.

  • User_7PZDE
    User_7PZDE Member Posts: 282 Blue Ribbon

    yes supp_id,part_id is my primary key in this scenario

  • mathguy
    mathguy Member Posts: 10,151 Blue Diamond
    select supp_id, part_id, status, date1, comments, rec_id, flag
    from   supp_dept
    match_recognize(
        partition by supp_id, part_id
        order     by date1
        measures  initcap(classifier()) as flag
        all rows  per match
        pattern   (^ old* new $)
        define    old as null is null
    );
    
    
       SUPP_ID PART_ID      STATUS     DATE1    COMMENTS     REC_ID FLAG
    ---------- ------------ ---------- -------- -------- ---------- ----
        178845 GE_2342      risk       12/12/20 test-22           3 New
        178845 Horizon_234  risk       12/12/21 test-21           0 Old
        178845 Horizon_234  risk       13/12/21 test-22           1 Old
        178845 Horizon_234  risk       14/12/21 test-23           2 New