finding multiple x with out knowing the value
drop table #temp
Select *
into #temp
from (
Select 1 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 2 as Column1, 'A' as Column2,'x' as Column3
Union all
Select 3 as Column1, 'B' as Column2,'x'as Column3
Union all
Select 4 as Column1, 'B' as Column2,'x' asColumn3
Union all
Select 5 as Column1, 'C' as Column2,'x'as Column3
Union all
Select 6 as Column1, 'D' as Column2,'y' as Column3
Union all
Select 7 as Column1, 'E' as Column2,'z' as Column3
) k
select *,ROW_NUMBER() over (partition by Column3 order by Column2)rn from #temp