Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in October 2006

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close