The following query lists the number of customers in each state:
SELECT state, COUNT(*) AS customers FROM customer GROUP BY state ORDER BY customers DESC
From this query I want to get the maximum number of customers and the second most number of customers in just one single query. I have tried using max(customers) and methods similar to this but violates the SQL syntax. How am I to do this all in one query?
If your database is Microsoft SQL Server or Access, you should use the TOP keyword:
select top 2 state , count(*) as customers from customer group by state order by customers desc
If your database is MySQL or PostgreSQL, you should use the LIMIT keyword:
select state , count(*) as customers from customer group by state order by customers desc limit 2
If your database is Oracle, you can use ROWNUM from a subselect:
select state , customers from ( select state , count(*) as customers from customer group by state order by customers desc ) where rownum <= 2
All of the above are likely more efficient than the generic "top n" approach. The generic "top n" query looks like this, assuming you want the top 2:
select foo , bar from sometable xxx where 2 > ( select count(*) from sometable where bar > xxx.bar ) order by bar desc
What the subquery does is count the number of rows that have a higher value than the value of the row of the outer query; if that number is less than two, then that row is one of the top two.
Applied to your problem, we have to use a derived table, since the values we're evaluating are counts themselves:
select state , customers from ( select state , count(*) as customers from customer group by state ) xxx where 2 > ( select count(*) from ( select state , count(*) as customers from customer group by state ) where customers > xxx.customers ) order by customers desc
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in October 2002