SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Distinct Vals in Col1 where Col2 never equals 1
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jethrow
Starting Member

USA
37 Posts

Posted - 08/11/2013 :  01:29:21  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
418 Posts

Posted - 08/11/2013 :  03:55:16  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 08/11/2013 :  15:05:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This
SELECT		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
Go to Top of Page

jethrow
Starting Member

USA
37 Posts

Posted - 08/15/2013 :  21:27:04  Show Profile  Reply with Quote
Thanks for the replies - your guys's SQL is way better

Microsoft SQL Server Noobie
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 08/16/2013 :  02:53:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT		Col1
FROM		@t
GROUP BY	Col1
HAVING		MIN(Col2) > 1;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jethrow
Starting Member

USA
37 Posts

Posted - 08/16/2013 :  03:01:12  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30208 Posts

Posted - 08/16/2013 :  03:46:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The go for my suggestion posted 08/11/2013 : 15:05:09


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000