Answer

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.

Rudy Limeback
By
Published: 26 Oct 2006

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.

Dig Deeper on Oracle development languages