Subset From Different Groups


http://forums.asp.net/t/2102617.aspx?Select+data+without+use+subselect

  
 
create table #a(product varchar(10),quality int)
insert into #a values('tri',2),('vse',2),('sve',2),('bun',1),('kal',3),('sve',3),('vse',3),('bun',2),('bun',1),('tri',3),('bun',3),('tri',3),('kal',2),('kal',1), ('kal111',1),('kal333',3)

-----Condition for select is: select all of product which are in quality 2 and 3 but not in quality 1. 
 
  
 --  option 1
 ;with mycte as (
select product, quality, Max( Case when quality=1 then POWER(2*1,1) 
when quality=2 then POWER(2*1,2) 
when quality=3 then POWER(2*1,3)  end) bits
from #a
group by product, quality
) 
,mycte1 as (
select  product, quality,
Case when  2 &  Sum( bits) Over(Partition by product) =2 then 1 else 0 End as val1 
,Case when  4 &  Sum(bits) Over(Partition by product) =4 then 1 else 0 End as val2 
,Case when  8 &  Sum(bits) Over(Partition by product) =8 then 1 else 0 End as val3 
from mycte)

Select distinct product from mycte1
WHERE val1=0 and val2=1 and val3=1


--option2 
Select product from (
select product from   #a
Where quality=2
intersect
select product from   #a
Where quality=3) t
except
select product from   #a
Where quality=1

 
drop table #a

 
 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s