Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Aged Yak Warrior

Romania
545 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
30421 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
30421 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
30421 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  
 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.05 seconds. Powered By: Snitz Forums 2000