August 11, 2009 at 11:12 am
I am trying to find a way to get results of ids that only have a value of z and no other value. The following is what I have come up with but my example table is, in my environment, a result of joining multiple table so I was looking for a more concise solution. any help would be appreciated.
create table #tmp(id int,
value char(1))
insert into #tmp
select 1,'A'
union all
select 1,'Z'
union all
select 1,'Z'
union all
select 1,'Z'
union all
select 2,'A'
union all
select 2,'B'
union all
select 3,'Z'
union all
select 4,'Z'
union all
select 4,'Z'
union all
select 4,'Z'
with z_cte as(select id from #tmp
group by id,value
having value = 'Z'),
a_cte as(
select id from #tmp
group by id,value
having value <> 'Z')
select z_cte.id from z_cte
left outer join a_cte
on z_cte.id = a_cte.id
where a_cte.id is null
August 11, 2009 at 11:45 am
Just wondering... is there a reason you can't do this?
select distinct id
from #tmp t1
where value = 'Z'
AND id NOT IN
( SELECT t2.id
FROM #tmp t2
WHERE t2.value 'Z'
)
August 11, 2009 at 11:58 am
August 11, 2009 at 12:07 pm
I definitely understand that desire, but I don't know of a way to do exactly that. After posting the last query, I realized, too, that there's a more efficient way to do it:
select distinct id
from #tmp t1
where value = 'Z'
AND NOT EXISTS
( SELECT 1
FROM #tmp t2
WHERE t2.value 'Z'
AND t2.id = t1.id
)
I'm not 100% certain, but pretty sure that EXISTS is more efficient than IN. Maybe someone else can help you on not querying the table more than once.
August 11, 2009 at 1:05 pm
The following query only queries #tmp once.
WITH Z_cte AS (
SELECT id FROM #tmp
GROUP BY id
HAVING Min(value) = 'Z'
AND Max(value) = 'Z'
)
SELECT z_cte.id
FROM z_cte
By only grouping on the ID instead of the ID and the Value, you can compare the minimum value and the maximum value to your desired value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 11, 2009 at 3:08 pm
Julie, you are correct that exists tends to be better performing.
Drew, thanks for that query. It is pretty much what I was looking and it helped me find another criteria that I over looked, nulls.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply