title: T-SQL - select top 10; one from each group
body: <p>Newbie here.</p>
  
  <p>I have list of companies with state_name (and other now unrelevant data). I'm trying to write a query which returns a top 10 list where companies appear only once with the state_name and COUNT(<em>) where COUNT(</em>) is the highest.</p>
  
  <p>This is where I am at the moment:</p>
  
  <pre><code>SELECT TOP 10
      COUNT(*) [# of Complaints]      
      ,state_name
      ,company
  FROM dbo.Consumer_Complaints
  GROUP BY company,state_name
  ORDER BY [# of Complaints] DESC
  </code></pre>
  
  <p>This returns the following:</p>
  
  <pre><code># of Complaints state_name company
  --------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  801             CA         Bank of America
  776             CA         Wells Fargo &amp; Company
  648             TX         Experian
  594             CA         Experian
  580             TX         Equifax
  566             FL         Bank of America
  539             CA         JPMorgan Chase &amp; Co.
  518             CA         Equifax
  499             FL         Wells Fargo &amp; Company
  481             TX         TransUnion Intermediate Holdings, Inc.
         ...
  </code></pre>
  
  <p>Ideally this is how it would look like in the end:</p>
  
  <pre><code> # of Complaints state_name company
   --------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      801             CA         Bank of America
      776             CA         Wells Fargo &amp; Company
      648             TX         Experian
      580             TX         Equifax
      539             CA         JPMorgan Chase &amp; Co.
      481             TX         TransUnion Intermediate Holdings, Inc.
     ...
  </code></pre>
  
  <p>What would be the ideal solution?</p>