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

    top 3 records query for 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)

    Raj

  2. #2
    Join Date
    May 2002
    Posts
    4
    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

  3. #3
    Join Date
    May 2002
    Posts
    4
    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

  4. #4
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    I don't think you can use the TOP command with Oracle as well.

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

  5. #5
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  7. #7
    Join Date
    Jul 2002
    Location
    India
    Posts
    13
    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
    /
    Balaji

Posting Permissions

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