where exists( select 1 from tb b where b.d=a.d and b.a>0) from tb a
select * from tb a where exists( select 1 from tb b where b.d=a.d and b.a>0)
select * from tb where d in(select d from tb where a>0 group by d having count(1)>2)
create table #temp(a varchar(10), b varchar(10), c varchar(10), d varchar(10), e varchar(10)) insert #temp select '1', 's', 's', '5', 'u' union all select '0', 's', 'p', '5', 'a' union all select '-1', 'e', 'w', '6', 'w' union all select '8', 'w', 'x', '6', 'p' union all select '6', 'w', 'x', '7', 'p' --SQL: ;with cte as ( SELECT rowno = ROW_NUMBER() over(order by getdate()), * FROM #TEMP ) select a,b,c,d,e from cte t WHERE t.a <= 0 or not exists(SELECT 1 FROM cte WHERE d = t.d and rowno <> t.rowno) /* a b c d e 0 s p 5 a -1 e w 6 w 6 w x 7 p */
where exists( select 1 from tb b where b.d=a.d and b.a>0)
*
from
tb
where
d in(select d from tb where a>0 group by d having count(1)>2)
insert #temp
select '1', 's', 's', '5', 'u' union all
select '0', 's', 'p', '5', 'a' union all
select '-1', 'e', 'w', '6', 'w' union all
select '8', 'w', 'x', '6', 'p' union all
select '6', 'w', 'x', '7', 'p'
--SQL:
;with cte as
(
SELECT rowno = ROW_NUMBER() over(order by getdate()), * FROM #TEMP
)
select a,b,c,d,e from cte t
WHERE t.a <= 0 or not exists(SELECT 1 FROM cte WHERE d = t.d and rowno <> t.rowno)
/*
a b c d e
0 s p 5 a
-1 e w 6 w
6 w x 7 p
*/