Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    38

    Unanswered: SQL Query to Return Top 50 Companies in Each City

    Microsoft Access 2007

    I have a table that had different cities, companies within each city, and the gross profit of each company.

    I'm trying to write a sql query that will return the companies with the top 50 profits in each city. So the output will show 50 records for each city sorted by the company with the highest profit to the company with the 50th highest profit.

    I started trying to write a query and I figured I would need a subquery. I have some experience in sql in Oracle SQL Developer but I'm having trouble understanding the Microsoft Access sql syntax (I've been using some articles on msdn.microsoft.com).

    If anyone could give my some starting points or links to good subquery articles that'd be great.

    So far I have:

    SELECT *
    FROM COMPANIES
    (SELECT * FROM Cities ORDER BY Gross Profit)
    WHERE ------------
    ORDER BY Cities, Gross Profit;

    I think I'm primarily having trouble with the WHERE clause and how to specify 50.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which table is the profit column in?

    why do you need both cities and companies table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    38
    Sorry about that, there's only one table and it's COMPANIES:

    SELECT *
    FROM COMPANIES
    (SELECT * FROM COMPANIES ORDER BY Gross Profit)
    WHERE ------------
    ORDER BY Cities, Gross Profit;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT city
         , company
         , gross_profit
      FROM companies AS t
     WHERE ( SELECT COUNT(*) 
               FROM companies  
              WHERE city = t.city
                AND gross_profit > t.gross_profit ) < 50
    ORDER
        BY city
         , gross_profit DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2012
    Posts
    38
    And yet another piece of my soul officially belongs to dbforums. Thanks a lot r937!

Posting Permissions

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