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

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close