8 Replies Latest reply: Jan 28, 2013 9:14 AM by 968034 RSS

    Matching data

    968034
      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
          HAVING COUNT() > 1
          • 2. Re: Matching data
            SomeoneElse
            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
              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
                Thanks.
                • 5. Re: Matching data
                  968034
                  Thanks.
                  • 6. Re: Matching data
                    968034
                    Thanks.
                    • 7. Re: Matching data
                      968034
                      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
                        New requirement.