Ask the Expert

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?


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: