Isn't it general to any RDBMS?

SELECT TOP 3 country, cnt FROM
(SELECT country, count(*) cnt FROM mobilePartners GROUP BY country)
x ORDER BY cnt DESC

I don't think you can use the TOP command with Oracle as well.

So rajdeo, what answer did you come up with ???

This one should work:

SELECT country, count(*) number
FROM mobilePartners
GROUP BY country
ORDER BY 2 DESC

You will have a full list though...

This one works with Informix and I think also with DB2.

create view countrypartners
( country, pnames )
as select country, count(*)
from mobilePartners
group by country

select country, pnames
from countrypartners CP
where 3 >
( select count(*)
from countrypartners
where pnames > CP.pnames )
order by pnames desc

the classic form of the "top n" query is based on a column value

in this case that column value is actually an aggregate

the approach that's guaranteed to work is to create countrypartners as a table, temporary if desired

i would bet a coffee and donut on the view approach working in all databases that support views and subselects

least likely in my opinion to run, but semantically valid, would be

select country, pnames
from (
select country
, count(*) as pnames
from mobilePartners
group by country ) CP
where 3 >
( select count(*)
from (
select country
, count(*) as pnames
from mobilePartners
group by country )
where pnames > CP.pnames )
order by pnames desc


rudy
http://rudy.ca/

I Hope the following query works with Oracle and SQL Server but i'm not sure with other databases.

Select Country, No From
(
Select Country , Count(*) as NO From mobilePartners Group by Country
) A Where 3 > (
Select Count(*) From (
Select Country , Count(*) as NO From mobilePartners Group by Country
) B
where B.No > A.No
)
Order by No desc
/