Try
;with cte as (select *, count(Symbol) over (partition by [Name]) as Cnt from Symbology)
select * from cte where Cnt > 1
>symbology table as
>
>symbol char(6)
>name varchar(100)
>
>I want to select all the symbols that belong to names that have at least one match ( on the entire name )
>
>so if I have
>
>ATT American Telephone and Telegraph
>ATT& American Telephone and Telegraph
>ATTB American Telephone and Telegraph
>AG American Greetings
>AA American Airlines
>AIR Associated Industry Resources
>AUR Associated Urban Renwal
>
>I only want to see
>
>ATT
>ATT&
>ATTB
>
>in the result set. Right now I am getting AG, AA AIR and AUR as they have matchs on the first three character.
>
>I want to group by name so that the only group having count(symbol) > 1 in the set above is the first three
>
>
>>Can you please translate in English what do you want to achieve? Better provide input and desired output.
>>
>>>I want to find all rows representing records that have at least one exact match on a varchar(100) column
>>>
>>>I am current doing
>>>
>>>
>>>SELECT
>>> name
>>> ,symbol
>>> ,from_date
>>> ,thru_date
>>> FROM [iSource100].[dbo].[Symbology]
>>> where exists ( select Name, COUNT(symbol) as cntsym from Symbology
>>> where thru_date > GETDATE()
>>> group by Name having COUNT(symbol) > 1 )
>>> and thru_date > GETDATE()
>>>order by name
>>>
>>>
>>>but from the results I'm getting it appears a 'match' for grouping seems to be based on just the first three characters of the Name column.
>>>
>>>How can I tell it to group by the entire contents of Name being an exact match?
>>>
>>>TIA
If it's not broken, fix it until it is.
My Blog