CODEWITHSUNDEEP
sql
user3052350
user3052350

Reputation: 31

Query results with the highest number

I wrote the following query that counts the "type" of subscription for each customer and displays the subscription name along with the number of customers associated with it. It works.

For example:

Subscription A = 200 customers Subscription B = 57

select 
   subscription_name, count(subscription_name) as Num_of_Customers 
from 
  subscription
    join customer on subscription.subscription_number=customer.subscription_number
group by subscription_name;

What I can't figure out is how to display "ONLY" the subscription name that has the highest number of customers.

I would greatly appreciate your advice. Thanks.

Upvotes: 2

Views: 106

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

You can just order the results by the number of customers and limit the result to the top row:

You don't mention what DBMS you are using, and this is done differently across different systems:

MYSQL - Use LIMIT 1 After the ORDER BY:

select subscription_name, count(subscription_name) as Num_of_Customers
from subscription
join customer on subscription.subscription_number = customer.subscription_number
group by subscription_name
order by count(subscription_name) desc
limit 1;

SQL-SERVER - Use SELECT TOP 1

select top 1 subscription_name, count(subscription_name) as Num_of_Customers
from subscription
join customer on subscription.subscription_number = customer.subscription_number
group by subscription_name
order by count(subscription_name) desc;

Oracle - USE ROWNUM

select * from  
( select subscription_name, count(subscription_name) as Num_of_Customers
    from subscription
    join customer on subscription.subscription_number = customer.subscription_number
    group by subscription_name
    order by count(subscription_name) desc 
) a
where ROWNUM = 1;

If you are using other DBMS you can see the idea on how to do it also.

Upvotes: 1

Related Questions