Q

Finding the highest COUNT in a GROUP BY query

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

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close