Select top 3 most count group by - SQL
11,842
Solution 1
Depending on what RDBMS you are using:
SQL SERVER:
SELECT TOP 3 COUNTRY, count(*)
FROM DRUG_SEIZURE
WHERE COUNTRY IS NOT NULL
GROUP BY COUNTRY
ORDER BY count(*) DESC
MySQL:
SELECT COUNTRY, count(*)
FROM DRUG_SEIZURE
WHERE COUNTRY IS NOT NULL
GROUP BY COUNTRY
ORDER BY count(*) DESC
LIMIT 3
Oracle:
SELECT *
FROM (
SELECT COUNTRY, count(*)
FROM DRUG_SEIZURE
WHERE COUNTRY IS NOT NULL
GROUP BY COUNTRY
ORDER BY count(*) DESC
) mr
WHERE rownum <= 3
ORDER BY rownum;
Solution 2
SELECT *
FROM (SELECT COUNTRY,count(*)
FROM DRUG_SEIZURE
WHERE COUNTRY IS NOT NULL
GROUP BY COUNTRY
ORDER BY 2 DESC)
WHERE rownum <= 3;
Solution 3
SELECT TOP 3 COUNTRY, COUNT(*)
FROM DRUG_SEIZURE
WHERE COUNTRY IS NOT NULL
GROUP BY COUNTRY
ORDER BY COUNT(*) DESC
Author by
user3400389
Updated on June 05, 2022Comments
-
user3400389 almost 2 years
I have an
sql
statement below which do group based on country names.SELECT COUNTRY,count(*) FROM DRUG_SEIZURE WHERE COUNTRY IS NOT NULL GROUP BY COUNTRY
Result Sample:
Country Count ------- ----- America 20 Saudi Arabia 28 China 10 Japan 14 Kenya 10 Pakistan 12 India 11
I want the top three max value countries. In the above case i only want:
Country Count ------- ----- Saudi Arabia 28 America 20 Japan 14