sql

52207

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

SomeoneElse

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

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

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

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

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

I've always considered set operators to be too heavy for performance. I don't generally think about SETs.

Cheers
Sarma.

jeneesh

Ummm....
Agreed...

BluShadow

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