alter procedure sp
    as
    begin

    with CTE_RN as
    (
    select d.name as Distributor,r.name as Reseller,count(r.childdistributorid) over (partition by r.childdistributorid) as Rcount from distributor d
    join reseller r on r.childdistributorid=d.id group by
    d.name,r.name,r.childdistributorid
    )
    select * from CTE_RN  group by  Distributor,Reseller having Rcount=max(Rcount)
    end
    exec sp