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
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;
SELECT *
FROM (SELECT COUNTRY,count(*)
FROM DRUG_SEIZURE
WHERE COUNTRY IS NOT NULL
GROUP BY COUNTRY
ORDER BY 2 DESC)
WHERE rownum <= 3;
SELECT TOP 3 COUNTRY, COUNT(*)
FROM DRUG_SEIZURE
WHERE COUNTRY IS NOT NULL
GROUP BY COUNTRY
ORDER BY COUNT(*) DESC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With