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 first published in October 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close