Ruthra Ruthra - 6 months ago 11
SQL Question

Guidance required for sql query

I have a database with one table as shown below. Here I'm trying to write a query to display the names of medication manufactured by the company that manufactures the most number of medications.

By looking at the table we could say the medication names which belongs to the company id 1 and 2 - because those company manufactures the most medication according to this table, but I'm not sure how to write a query for selecting the same i said before.

ID | COMPANY_ID | MEDICATION_NAME

1 1 ASPIRIN

2 1 GLUCERNA

3 2 SIBUTRAMINE

4 1 IBUPROFEN

5 2 VENOFER

6 2 AVONEN

7 4 ACETAMINOPHEN

8 3 ACETAMINO

9 3 GLIPIZIDE


Please share your suggestions. Thanks!

Answer

Several ways to do this. Here's one which first uses a subquery to get the maximum count, then another subquery to get the companies with that count, and finally the outer query to return the results:

select * 
from yourtable
where companyid in (
  select companyid
  from yourtable
  group by companyid
  having count(1) = (
    select count(1) cnt 
    from yourtable
    group by companyid
    order by 1 desc
    limit 1
    )
)