Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    4

    Red face Unanswered: top 3 rows query which can be run on any database

    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)

    I tried using GROUP BY clause in following query :-

    SELECT a.COUNTRY, count(a.id)
    from tblpartner a
    GROUP by a.COUNTRY
    having 3 >
    (SELECT count(b.id)
    from tblpartner b
    GROUP by b.COUNTRY HAVING count(a.id) < count(b.id))

    but getting error - 'Subquery returned more than 1 value. This is not permitted'

    any clues ?

    Raj

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Try this...
    --------------------------------------------------------------------------------------------
    create table #tblpartner(ID int, PNAME varchar(10), COUNTRY varchar(10))
    insert into #tblpartner values(1,'A1','AA')
    insert into #tblpartner values(2,'A2','AA')
    insert into #tblpartner values(3,'A3','AA')
    insert into #tblpartner values(4,'A4','AA')
    insert into #tblpartner values(5,'A5','AA')
    insert into #tblpartner values(6,'A6','AB')
    insert into #tblpartner values(7,'A7','AB')
    insert into #tblpartner values(8,'A8','AB')
    insert into #tblpartner values(9,'A9','AB')
    insert into #tblpartner values(10,'A10','AB')
    insert into #tblpartner values(11,'A11','AC')
    insert into #tblpartner values(12,'A12','AC')
    insert into #tblpartner values(13,'A13','AC')
    insert into #tblpartner values(14,'A14','AC')
    insert into #tblpartner values(15,'A15','AC')
    insert into #tblpartner values(16,'A16','AC')
    insert into #tblpartner values(17,'A17','AC')
    insert into #tblpartner values(18,'A18','AD')
    insert into #tblpartner values(19,'A19','AD')
    insert into #tblpartner values(20,'A20','AD')
    insert into #tblpartner values(21,'A21','AA')

    select TOP 3 country, count(*) as '# of Partners' from #tblpartner group by country order by 2 desc
    --------------------------------------------------------------------------------------------

    Should produce this...
    --------------------------------------------------------------------------------------------
    country # of Partners
    ---------- -------------
    AC 7
    AA 6
    AB 5
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •