This discussion is archived
8 Replies Latest reply: Jan 28, 2013 7:14 AM by 968034 RSS

Matching data

968034 Newbie
Currently Being Moderated
Oracle 11g on Linux
I have the following table.
create table p
    (pid varchar2(10),
     first_name varchar2(10),
      last_name varchar2(10),
      type_code varchar2(5)      
      );
     
 insert into p values  ('1A', 'DENNIS', 'ALRIDGE', 'G5');
 insert into p values ('2A','MARTIN','SMITH','G2' );
 insert into p values ('3A', 'DENNIS','ALRIDGE','G5');
 
 select distinct * from p;

PID     FIRST_NAME     LAST_NAME     TYPE_CODE
2A     MARTIN               SMITH           G2
3A     DENNIS               ALRIDGE       G5      
1A     DENNIS               ALRIDGE       G5

I would like to get only the rows 1A and 3A


 PID  FIRST_NAME    LAST_NAME     TYPE_CODE
 1A   DENNIS         ALRIDGE          G5
 3A   DENNIS         ALRIDGE          G5

 i.e. where FIRST_NAME, LAST_NAME and TYPE_CODE are the same, but PID is DIFFERENT 
The production table has thousands of rows so it's not possible to simple select the pids I want.  

Thanks,

Dane
  • 1. Re: Matching data
    sb92075 Guru
    Currently Being Moderated
    HAVING COUNT() > 1
  • 2. Re: Matching data
    SomeoneElse Guru
    Currently Being Moderated
    select *
    from   p p1
    where  exists (select 1
                   from   p p2
                   where  p1.first_name = p2.first_name
                   and    p1.last_name  = p2.last_name
                   and    p1.type_code  = p2.type_code
                   and    p1.pid       != p2.pid
                  );
  • 3. Re: Matching data
    SamFisher Explorer
    Currently Being Moderated
    SELECT *
      FROM (SELECT pid,
                   first_name,
                   last_name,
                   type_code,
                   COUNT (*) OVER (PARTITION BY first_name, last_name, type_code)
                      cnt
              FROM p)
     WHERE cnt > 1;
  • 4. Re: Matching data
    968034 Newbie
    Currently Being Moderated
    Thanks.
  • 5. Re: Matching data
    968034 Newbie
    Currently Being Moderated
    Thanks.
  • 6. Re: Matching data
    968034 Newbie
    Currently Being Moderated
    Thanks.
  • 7. Re: Matching data
    968034 Newbie
    Currently Being Moderated
    I have a new requirement for this query to separate pid into pid1 and pid2 to see the duplicates in row format i.e..
    
     PID  FIRST_NAME    LAST_NAME     TYPE_CODE
     1A   DENNIS               ALRIDGE          G5
     3A   DENNIS               ALRIDGE          G5
    
    becomes . . .
    
     PID1  FIRST_NAME1    LAST_NAME1     TYPE_CODE1                      PID2  FIRST_NAME2    LAST_NAME2     TYPE_CODE2
     1A     DENNIS           ALRIDGE           G5                        3A    DENNIS         ALRIDGE          G5
    
    I tried mod(rownum,2) != 0 to separate the row numbers, but the output is offset with null like:
    
     PID1  FIRST_NAME1    LAST_NAME1     TYPE_CODE1                     PID2  FIRST_NAME2    LAST_NAME2     TYPE_CODE2
     1A         DENNIS           ALRIDGE       G5            
                                                                            3A    DENNIS         ALRIDGE          G5
    
    Any ideas how I can get the desired output?
    
    Thanks,
    
    Dane
  • 8. Re: Matching data
    968034 Newbie
    Currently Being Moderated
    New requirement.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points