The following query lists the number of customers in each state:
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.