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.
Related Q&A from Rudy Limeback
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue 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.