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.
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 MYNAMES1 .1 AAA1 BBB2 .3 .4 .4 BBBWhat 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 selectID MYNAMES1 AAA1 BBB2 .3 .4 BBBI 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 @SampleVALUES (1, '.'), (1, 'AAA'), (1, 'BBB'), (2, '.'), (3, '.'), (4, '.'), (4, 'BBB');-- SwePesoWITH cteSource(ID, MyNames, Yak)AS ( SELECT ID, MyNames, MAX(MyNames) OVER (PARTITION BY ID) AS Yak FROM @Sample)SELECT ID, MyNamesFROM cteSourceWHERE MyNames <> '.' OR Yak = '.'[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
sapator
Constraint Violating Yak Guru
462 Posts |
Posted - 2013-03-23 : 18:27:13
|
Ah, that is perfect.Thanks! |
|
|
|
|
|
|
|