title: Retrieve list of items from table which has max count
body: <p>I have 2 tables <code>Distributor</code> and <code>Reseller</code>, and the have columns like</p>
  
  <p><strong>Distributor</strong></p>
  
  <pre><code>  id, name
  </code></pre>
  
  <p><strong>Reseller</strong> </p>
  
  <pre><code> Id name ChildDistriid 
  </code></pre>
  
  <p>I want to select details of distributor who have maximum Reseller. </p>
  
  <p>This is my code so far:</p>
  
  <pre><code>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
  </code></pre>