title: SQL - get the max value from a group by + count table
body: <p>I am referring to this data base: <a href="http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all" rel="nofollow">http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all</a></p>
  
  <p>I am trying to fulfill the following query:
  "Which supplier provides most products?"</p>
  
  <p>I came to this query</p>
  
  <pre><code>select SupplierName, max(count)
  from(
      select SupplierName, count(SupplierName) as count
      from 
          Products as p
          inner join 
          Suppliers as s
          on p.SupplierID = s.SupplierID
      group by SupplierName
  )
  </code></pre>
  
  <p>But the result is only half way right.</p>
  
  <pre><code>  SupplierName          |   max(count)
  _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
                          |
    Zaanse Snoepfabriek   |   5
  </code></pre>
  
  <p>The value 5 is right, but the SupplierName is just the last value of the inner SQL Query. I always though the max(count) statement automatically provides the correct SupplierName.
  Why isn't it like that?</p>
  
  <p>If I change the query to this, everything is alright:</p>
  
  <pre><code>select SupplierName, max(count)
  from(
      select SupplierName, count(SupplierName) as count
      from 
          Products as p
          inner join 
          Suppliers as s
          on p.SupplierID = s.SupplierID
      group by SupplierName
  ) t
  where t.count = 
  (
      select max(count)
      from(
          select SupplierName, count(SupplierName) as count
          from 
              Products as p
              inner join 
              Suppliers as s
              on p.SupplierID = s.SupplierID
          group by SupplierName
      ) 
  )
  </code></pre>
  
  <p>But this way I create a whole new table just to get the max and then compare it to t.count.
  If the data base is 1,000,000+ records things get serious.</p>
  
  <p>Is there a more elegant way to this problem:</p>
  
  <p>Count something then get the max of it, and get the correlated ID/Name to it.</p>