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 2008 Forums
 Transact-SQL (2008)
 select data with having count?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sapator
Constraint Violating Yak Guru

Greece
438 Posts

Posted - 03/23/2013 :  17:42:37  Show Profile  Reply with Quote
It's difficult to explain in the title.
I have rows such as:
ID MYNAMES
1 .
1 AAA
1 BBB
2 .
3 .
4 .
4 BBB

What i want to do is include all the rows but when a row has a dot and a row also has another MYNAMES on the SAME ID then i want to exclude the dot.
So the above would select

ID MYNAMES
1 AAA
1 BBB
2 .
3 .
4 BBB

I tried with having count but i don't think will work.
I can simply use where MYNAMES <> '.' but that will also exclude the rows that only have a dot and no other data, and i don't want to do that.
Any help?
Thanks.

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 03/23/2013 :  18:06:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		ID INT,
		MyNames VARCHAR(10)
	);

INSERT	@Sample
VALUES	(1, '.'),
	(1, 'AAA'),
	(1, 'BBB'),
	(2, '.'),
	(3, '.'),
	(4, '.'),
	(4, 'BBB');

-- SwePeso
WITH cteSource(ID, MyNames, Yak)
AS (
	SELECT	ID,
		MyNames,
		MAX(MyNames) OVER (PARTITION BY ID) AS Yak
	FROM	@Sample
)
SELECT	ID,
	MyNames
FROM	cteSource
WHERE	MyNames <> '.'
	OR Yak = '.'



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

sapator
Constraint Violating Yak Guru

Greece
438 Posts

Posted - 03/23/2013 :  18:27:13  Show Profile  Reply with Quote
Ah, that is perfect.
Thanks!
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.07 seconds. Powered By: Snitz Forums 2000