Author |
Topic  |
|
jethrow
Starting Member
USA
37 Posts |
Posted - 08/11/2013 : 01:29:21
|
I found this question in another forum - I was wondering if there was a better method, or what some other methods might be for this:quote: If i have a table like this: col1 col2 foo 1 foo 2 foo 3 bar 2 bar 3
How do a get all the distinct values in col1 for rows that are never equal to 1 in col2. In this case the query should return bar only because no row exists with bar and 1 together .
This was what I provided:
Declare @t table (col1 varchar(10), col2 int);
Insert Into @t Values
('foo', 1),
('foo', 2),
('foo', 3),
('bar', 2),
('bar', 3);
Select col1 From (
Select p1.col1, (
Select ','+Cast(col2 as varchar(10))+','
From @t As p2
Where p1.col1 = p2.col1
For XML PATH('') ) col2
From @t As p1
Group By col1 ) n
Where col2 Not Like '%,1,%'
Microsoft SQL Server Noobie |
Edited by - jethrow on 08/11/2013 02:05:30
|
|
stepson
Aged Yak Warrior
Romania
545 Posts |
Posted - 08/11/2013 : 03:55:16
|
Hi,
try this:
/*--this will give me all the distinct values with col2=1
select col1
from @t
where col2=1
group by col1
*/
/* now just remove somehow those values (with col2=1)*/
select col1
from @t as t1
where not exists (select col1 from @t as t2 where col2=1 and t1.col1=t2.col1 group by col1)
group by col1
-- or use of except
select col1
from @t as t1
group by col1
EXCEPT
select col1
from @t
where col2=1
group by col1
Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut sabinWeb |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/11/2013 : 15:05:09
|
ThisSELECT Col1
FROM @t
GROUP BY Col1
HAVING MAX(CASE WHEN Col2 = 1 THEN 1 ELSE 0 END) = 0;
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
jethrow
Starting Member
USA
37 Posts |
Posted - 08/15/2013 : 21:27:04
|
Thanks for the replies - your guys's SQL is way better
Microsoft SQL Server Noobie |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/16/2013 : 02:53:31
|
SELECT Col1
FROM @t
GROUP BY Col1
HAVING MIN(Col2) > 1;
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
jethrow
Starting Member
USA
37 Posts |
Posted - 08/16/2013 : 03:01:12
|
Thanks for the additional approach - though it's questionable how reliable it'd be since in theory Col2 could be < 1 w/o ever equaling 1. But, based on the original table ...
Microsoft SQL Server Noobie |
 |
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 08/16/2013 : 03:46:55
|
The go for my suggestion posted 08/11/2013 : 15:05:09
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
Topic  |
|
|
|