4 Replies Latest reply on Jan 22, 2013 2:18 PM by AlbertoFaenza

    extract set of records from group

    Naren
      Hi All,


      I have total 6 sets of codes from which i have to select a pair of records ('NAREN','SUREN'). Which ever code has these two sets of records associated that records should be
      pulled.

      please help with the query.

      Oracle version installed: 10.0.1

      sample dataset:

      WITH T AS
      (
      SELECT 1 ID,1 CD,'NAREN' NM FROM DUAL
      UNION
      SELECT 2 ID,1 CD,'SUREN' NM FROM DUAL
      UNION
      SELECT 3 ID,1 CD,'CHITTI' NM FROM DUAL
      UNION
      SELECT 4 ID,1 CD,'ABHI' NM FROM DUAL
      UNION
      SELECT 5 ID,2 CD,'VENKAT' NM FROM DUAL
      UNION
      SELECT 6 ID,2 CD,'RAJ' NM FROM DUAL
      UNION
      SELECT 7 ID,2 CD,'MAHESH' NM FROM DUAL
      UNION
      SELECT 8 ID,2 CD,'NAREN' NM FROM DUAL
      UNION
      SELECT 9 ID,3 CD,'NAREN' NM FROM DUAL
      UNION
      SELECT 10 ID,3 CD,'SUREN' NM FROM DUAL
      UNION
      SELECT 11 ID,4 CD,'SUREN' NM FROM DUAL
      UNION
      SELECT 12 ID,5 CD,'PATIL' NM FROM DUAL
      UNION
      SELECT 13 ID,6 CD,'RAJESH' NM FROM DUAL
      UNION
      SELECT 14 ID,6 CD,'MALLI' NM FROM DUAL
      UNION
      SELECT 15 ID,6 CD,'SANTHI' NM FROM DUAL
      UNION
      SELECT 16 ID,6 CD,'PRAVEENA' NM FROM DUAL
      )
      SELECT * FROM T





      Output required:
      ==============

      ID     CD     NAME
      1     1     NAREN
      2     1     SUREN
      9     3     NAREN
      10     3     SUREN


      Note: condition should match the set which exactly matches two values or (pair).

      Thanks,
      Naren
        • 1. Re: extract set of records from group
          AlbertoFaenza
          What about this?
          SELECT a.* FROM t a join t b 
          ON (  a.nm ='NAREN' and a.cd=b.cd and b.nm='SUREN'
             OR a.nm ='SUREN' and a.cd=b.cd and b.nm='NAREN')
          ORDER by a.id;
          
                  ID         CD NM      
          ---------- ---------- --------
                   1          1 NAREN   
                   2          1 SUREN   
                   9          3 NAREN   
                  10          3 SUREN
          Regards.
          Al
          • 2. Re: extract set of records from group
            chris227
            WITH T AS
            (
            SELECT 1 ID,1 CD,'NAREN' NM FROM DUAL
            UNION
            SELECT 2 ID,1 CD,'SUREN' NM FROM DUAL
            UNION
            SELECT 3 ID,1 CD,'CHITTI' NM FROM DUAL
            UNION
            SELECT 4 ID,1 CD,'ABHI' NM FROM DUAL
            UNION
            SELECT 5 ID,2 CD,'VENKAT' NM FROM DUAL
            UNION
            SELECT 6 ID,2 CD,'RAJ' NM FROM DUAL
            UNION
            SELECT 7 ID,2 CD,'MAHESH' NM FROM DUAL
            UNION
            SELECT 8 ID,2 CD,'NAREN' NM FROM DUAL
            UNION
            SELECT 9 ID,3 CD,'NAREN' NM FROM DUAL
            UNION
            SELECT 10 ID,3 CD,'SUREN' NM FROM DUAL
            UNION
            SELECT 11 ID,4 CD,'SUREN' NM FROM DUAL
            UNION
            SELECT 12 ID,5 CD,'PATIL' NM FROM DUAL
            UNION
            SELECT 13 ID,6 CD,'RAJESH' NM FROM DUAL
            UNION
            SELECT 14 ID,6 CD,'MALLI' NM FROM DUAL
            UNION
            SELECT 15 ID,6 CD,'SANTHI' NM FROM DUAL
            UNION
            SELECT 16 ID,6 CD,'PRAVEENA' NM FROM DUAL
            )
            , groups as (
            select
             cd
            from (
                select cd, nm from t
                where
                nm in ('NAREN','SUREN')
                )
                group by cd
                having
                  (count(distinct nm) >=2)
            )
            
            select
            *
            from t
            where
            cd in
                (select cd from groups    )
            and
            nm in ('NAREN','SUREN')
            
            ID     CD     NM
            1     1     NAREN
            2     1     SUREN
            9     3     NAREN
            10     3     SUREN
            1 person found this helpful
            • 3. Re: extract set of records from group
              Naren
              Thanks for quick reply
              • 4. Re: extract set of records from group
                AlbertoFaenza
                Another alternative:
                WITH T AS
                (
                SELECT 1 ID,1 CD,'NAREN' NM FROM DUAL
                UNION
                SELECT 2 ID,1 CD,'SUREN' NM FROM DUAL
                UNION
                SELECT 3 ID,1 CD,'CHITTI' NM FROM DUAL
                UNION
                SELECT 4 ID,1 CD,'ABHI' NM FROM DUAL
                UNION
                SELECT 5 ID,2 CD,'VENKAT' NM FROM DUAL
                UNION
                SELECT 6 ID,2 CD,'RAJ' NM FROM DUAL
                UNION
                SELECT 7 ID,2 CD,'MAHESH' NM FROM DUAL
                UNION
                SELECT 8 ID,2 CD,'NAREN' NM FROM DUAL
                UNION
                SELECT 9 ID,3 CD,'NAREN' NM FROM DUAL
                UNION
                SELECT 10 ID,3 CD,'SUREN' NM FROM DUAL
                UNION
                SELECT 11 ID,4 CD,'SUREN' NM FROM DUAL
                UNION
                SELECT 12 ID,5 CD,'PATIL' NM FROM DUAL
                UNION
                SELECT 13 ID,6 CD,'RAJESH' NM FROM DUAL
                UNION
                SELECT 14 ID,6 CD,'MALLI' NM FROM DUAL
                UNION
                SELECT 15 ID,6 CD,'SANTHI' NM FROM DUAL
                UNION
                SELECT 16 ID,6 CD,'PRAVEENA' NM FROM DUAL
                )
                , t1 as
                (
                SELECT id, cd, nm, COUNT(DISTINCT nm) OVER(PARTITION BY cd) cnt
                  FROM T
                 WHERE nm IN ('NAREN', 'SUREN')
                )
                select id, cd, nm 
                  from t1
                 WHERE cnt > 1;
                 
                        ID         CD NM      
                ---------- ---------- --------
                         1          1 NAREN   
                         2          1 SUREN   
                         9          3 NAREN   
                        10          3 SUREN   
                Regards.
                Al

                Edited by: Alberto Faenza on Jan 22, 2013 3:17 PM
                Modified count(*) with COUNT(DISTINCT nm)