Hi,

I have a table called mobilePartners which has 3 fields ie. ID, PNAME & COUNTRY.....

ID is unique for each partner.....one country can have many partners but one partner is associated with only one country......

I want to get the top 3 countries who have maximum no. of partners in the form of country name & no. of partners...is this possible using a query which can be applied to any RDBMS ? (i.e no RDBMS specific functions can be used)

Raj

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

Hi,

Your query doesn't run on DB2....it may run on SQL Server & MS-Access..but surely not on DB2..

thanks for the reply....anyway I have found out the query which runs on SQL server as well as DB2...

regards

Rajesh

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
/