Ask the Expert

Finding the highest COUNT in a GROUP BY query

I retrieve two columns named SuperVisor and totalcontracts using this query:

select SuperVisor,
       count(ContractNo) as totalcontracts
from Contract
group by SuperVisor

Now I want to get the maximum of totalcontracts from the retrieved data using a select statement.

    Requires Free Membership to View

Obtaining just the maximum total is simple. Just use your first COUNT query as a derived table in a new MAX query:

select max(totalcontracts) as highest_total
  from (
       select SuperVisor                               
            , count(ContractNo) as totalcontracts       
         from Contract                                    
       group 
           by SuperVisor                              
       ) as t

Remember, you can use a query anywhere you can use a table, provided that you obey a few simple syntactical rules such as enclosing it in parentheses and assigning a table alias.

But what if you want to know which supervisor achieved this highest total count?

There's a hard way and an easy way. The hard way is like this:

select SuperVisor
     , count(ContractNo) as totalcontracts
  from Contract
group
    by SuperVisor
having count(ContractNo) =
     ( select max(totalcontracts) as highest_total
         from (
              select SuperVisor
                   , count(ContractNo) as totalcontracts
                from Contract
              group
                  by SuperVisor
              ) as t
     )

The easy way is simply to sort the MAX query results into descending order by totalcontracts and use TOP or LIMIT or a similar mechanism. Note that this would not be standard SQL, and you must also watch out for ties yourself; the hard way above handles ties properly.

This was first published in October 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: