Results 1 to 2 of 2
  1. #1
    pinecrest515 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    31

    Interesting Query Problem (Sample Data Provided)

    Dear Forumers,



    I would like to consult you on an interesting problem, and hopefully brush up on my Access SQL skills.

    I am trying to get a report to output the top 10 sales items by dept, category, and sub-category level. To illustrate the problem without using actual company data, I created a sample problem and data set (see below).

    Problem: Suppose I have a listing of player ratings in basketball and soccer (under Sports category) and U.S./European leagues (under League category). I want to get the following:
    1) Highest 2 rated players in each league for each sport
    2) Highest 2 rated players in each sport
    3) Highest 2 rated players overall

    How do I construct the SQL? I want to display the highest 2 rated players in each league for each sport together in the same output so that they can be compared. Ideally, since the real data is huge, I don't want to construct a separate view just for ranking purposes.

    Sample data:

    Area Sport League Player Rating
    Sports B-Ball NBA Duncan 90
    Sports B-Ball NBA James 99
    Sports B-Ball NBA Wade 97
    Sports B-Ball NBA Bryant 99
    Sports B-Ball Euro Rubio 83
    Sports B-Ball Euro Navarro 85
    Sports B-Ball Euro Lull 79
    Sports B-Ball Euro Vasquez 78
    Sports Soccer MLS Donovan 81
    Sports Soccer MLS Beckham 80
    Sports Soccer MLS Henry 82
    Sports Soccer MLS Marquez 78
    Sports Soccer Liga Messi 99
    Sports Soccer Liga Iniesta 91
    Sports Soccer Liga Xavi 90
    Sports Soccer Liga Ronaldo 96



    Ideally, the result should look like this:
    Sports B-Ball NBA James 99
    Sports B-Ball NBA Bryant 99
    Sports B-Ball Euro Rubio 83
    Sports B-Ball Euro Navarro 85
    Sports Soccer MLS Donovan 81
    Sports Soccer MLS Henry 82
    Sports Soccer Liga Messi 99
    Sports Soccer Liga Ronaldo 96


    I tried the following query, but got an error in Access saying it does not recognize RANK() OVER PARTITION BY function. Does RANK() PARTITION BY work in Access? If not, is there an alternative to it?

    SELECT * FROM
    (SELECT Area, Sport, League, Player, RANK() OVER (PARTITION BY Area, Sport, League, Player ORDER BY Rating) AS RN FROM Sheet1) As RK
    WHERE RK.RN <= 2

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    I dont believe the RANK() function works in Access.

    What you can do is
    SELECT TOP 2 *
    FROM myTable
    GROUP BY League
    ORDER BY Rating DESC
    UNION
    SELECT TOP 2 *
    FROM myTable
    GROUP BY Sport
    ORDER BY Rating DESC
    UNION
    SELECT TOP 2 *
    FROM myTable
    ORDER BY Rating DESC;

    Give that a whirl and see what happens. SQL not tested, so fix any syntax errors you see.
    NOTE: This requires that no two sports have the same league name.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help! Another Interesting Problem
    By pinecrest515 in forum Queries
    Replies: 14
    Last Post: 12-15-2010, 01:53 PM
  2. Interesting Problem
    By pinecrest515 in forum Programming
    Replies: 1
    Last Post: 12-15-2010, 01:19 PM
  3. Help! Very interesting query problem
    By pinecrest515 in forum Queries
    Replies: 5
    Last Post: 12-15-2010, 11:46 AM
  4. Help with Query!!! (Interesting Problem)
    By pinecrest515 in forum Queries
    Replies: 4
    Last Post: 12-09-2010, 08:20 AM
  5. Interesting Query Problem
    By Lockrin in forum Queries
    Replies: 7
    Last Post: 08-23-2010, 01:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums