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