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
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 ) )