Forum Stats

  • 3,734,025 Users
  • 2,246,861 Discussions
  • 7,857,001 Comments

Discussions

sql

52207
52207 Member Posts: 544
edited February 2007 in SQL & PL/SQL
scenario where need to find records where a particular id has 2 rows for code='X' and one row for code='Y' combined together.

So from the following data, only id 1 should be returned.

ID CODE
-------
1 X
1 X
1 Y

2 X
2 Y

3 X
3 X
3 Y
3 Y

4 X
4 X

5 Y
5 Y

Comments

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    SQL> select * from t1;

    ID C
    -------------------- -
    1 X
    1 X
    1 Y
    2 X
    2 Y
    3 X
    3 X
    3 Y
    3 Y
    4 X
    4 X
    5 Y
    5 Y

    13 rows selected.

    SQL> select id
    2 from t1
    3 where code = 'X'
    4 group by id
    5 having count(*) = 2
    6 intersect
    7 select id
    8 from t1
    9 where code = 'Y'
    10 group by id
    11 having count(*) = 1;

    ID
    --------------------
    1
  • 519688
    519688 Member Posts: 2,646
    somewhat simpler:

    select id
    from t1
    where code in ('X','Y')
    group by id
    having sum( decode(code,'X',1,0) ) = 2
    and sum( decode(code,'Y',1,0) ) = 1
  • 52207
    52207 Member Posts: 544
    There is one more scenario added to this, the account number.

    Only if the account number is different in code "X" , the record should be return, for example

    ID CODE ACCNO
    -----------------------------
    1 X 1112
    1 X 1113
    1 Y 1112

    2 X 1112
    2 X 1112
    2 Y 1112

    3 X 1112
    3 Y 1113

    Only ID 1 should be returned because the accno within X is different and count is 2.
  • RadhakrishnaSarma
    RadhakrishnaSarma Member Posts: 2,900
    May be an in-line view with analytics?
    select id
    from (select id, 
                 code,
                 acctno,
                 row_number() over (partition by id, 
                                                 code, 
                                                 acctno 
                                    order by code) rn
          from t1
          )
    where rn = 1
    and code in ('X','Y')
    group by id
    having sum( decode(code,'X',1,0) ) = 2
    and sum( decode(code,'Y',1,0) ) = 1
    Cheers
    Sarma.
  • jeneesh
    jeneesh Member Posts: 7,168
    edited February 2007
    Or an intersect
    sql>select * from t;
    ID CODE ACCNO
    1 X 1112
    1 X 1113
    1 Y 1112
    2 X 1112
    2 X 1112
    2 Y 1112
    3 Y 1113
    3 X 1112

    sql>
    select id
    from t
    where code = 'X'
    having count(distinct accno) = 2
    group by id
    intersect
    select id
    from t
    where code = 'Y'
    having count(accno) = 1
    group by id;
    ID
    1

    jeneesh
  • RadhakrishnaSarma
    RadhakrishnaSarma Member Posts: 2,900
    I've always considered set operators to be too heavy for performance. I don't generally think about SETs.

    Cheers
    Sarma.
  • jeneesh
    jeneesh Member Posts: 7,168
    Ummm....
    Agreed...
  • BluShadow
    BluShadow Member, Moderator Posts: 40,986 Red Diamond
    edited February 2007
    Or something a little more convoluted...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t1 as (select 1 as id, 'X' as code, 1112 as acc_no from dual union all
      2  select 1, 'X', 1113 from dual union all
      3  select 1, 'Y', 1112 from dual union all
      4  select 2, 'X', 1112  from dual union all
      5  select 2, 'X', 1112 from dual union all
      6  select 2, 'Y', 1112 from dual union all
      7  select 3, 'X', 1112  from dual union all
      8  select 3, 'Y', 1113 from dual)
      9  -- END OF TEST DATA
     10  select id
     11  from (select id, code
     12              ,decode(lead(acc_no,1) over (partition by id, code order by id, code),
     13                      acc_no, null, decode(code, 'X', 1)) as same_acc
     14        from t1)
     15  where code in ('X','Y')
     16  group by id
     17  having sum( decode(code,'X',1,0) ) = 2
     18  and sum( decode(code,'Y',1,0) ) = 1
     19* and sum(same_acc) = 2
    SQL> /
    
            ID
    ----------
             1
    
    SQL>
    PS. ignore the name "same_acc", it should really be "diff_acc" :)

    Message was edited by:
    blushadow
This discussion has been closed.