select *
from geodata.TB_City A inner join
(select citydefaultname, CityCountryID, avg(CityLatitude) 'CityLatitude', avg(CityLongitude) 'CityLongitude' from geodata.TB_City
group by citydefaultname, CityCountryID) B on
A.CityCountryID = b.CityCountryID and a.CityDefaultName = b.CityDefaultName and ISNULL(ISNULL(a.CityProvinceID_3,a.CityProvinceID_2),a.CityProvinceID_1) = ISNULL(ISNULL(b.CityProvinceID_3,b.CityProvinceID_2),b.CityProvinceID_1)
I want to use condition on CityProvinceID_3,CityProvinceID_2,CityProvinceID_1 but group by only on citydefaultname,CityCountryID