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 and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.