title: SELECT MAX of COUNT
body: <p>I have a table "well". It contains a column app_rate_unit (type: nvarchar).
  My goal is to count every distinct value in the table and let the DBMS (MS Server 2005) give me the most occurring one.</p>
  
  <p>This is my code:</p>
  
  <pre><code>SELECT MAX(app_rate_unit) AS MAX_APP
    FROM (SELECT app_rate_unit, COUNT(*) AS co
            FROM dbo.well AS w
           GROUP BY app_rate_unit
          ) AS derivedtbl_1
  </code></pre>
  
  <p>The poblem with it is however, that my DBMS actually delivers the lowest count to me.</p>
  
  <p>SideQuestion: How do I filter for a foreign key (in the table) and NOT NULL (in app_rate_unit) when counting?</p>