Q

TOP 2 results in just one query

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


This was last published in October 2002

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