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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select data with having count?

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2013-03-23 : 17:42:37
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

30421 Posts

Posted - 2013-03-23 : 18:06:01
[code]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 = '.'[/code]


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

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2013-03-23 : 18:27:13
Ah, that is perfect.
Thanks!
Go to Top of Page
   

- Advertisement -