Access Record Ranking by Group and Percent

dhemple
dhemple used Ask the Experts™
on
Hello Experts

I need your help with an update query that will populate the Mkt Share Ranking column in the example below.  Highest market share would get a ranking of 1, the next highest a ranking of 2, etc.

Thank you.

GroupId      Mkt Share Percent      Mkt Share Ranking
a1567      32%      2
a1567      10%      3
a1567      58%      1
b4382      12%      3
b4382      25%      2
b4382      63%      1
c9253      15%      3
c9253      12%      4
c9253      27%      2
c9253      46%      1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
test this

SELECT TableX.GroupID, TableX.[Mkt Share Percent], (select Count(*) from TableX as Y where Y.GroupID=TableX.GroupID And Y.[Mkt Share Percent]>=TableX.[Mkt Share Percent]) AS [Mkt Share Ranking]
FROM TableX
ORDER BY TableX.GroupID, TableX.[Mkt Share Percent] DESC;


also, it is not advisable to store calculated values to the table, you can always get the correct values running the query in case the values for the Mkt Share Percent changed

Author

Commented:
Thanks for your response Rey.

I must have something wrong.  When I put in the actual table and field names here's what I have.  I'm getting an error.  Can you help me understand what I'm doing wrong?  Thanks

SELECT [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct, (select count(*) from [BidFileTable-OutputForIT] as Y where Y.drug_id_38= [BidFileTable-OutputForIT]drug_id_38 and Y.market_share_percent >=  [BidFileTable-OutputForIT].market_share_pct) AS [market_share_rank]
FROM [BidFileTable-OutputForIT]
ORDER BY [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct DESC;
Top Expert 2016
Commented:
try this


SELECT [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct, (select count(*) from [BidFileTable-OutputForIT] as Y where Y.drug_id_38= [BidFileTable-OutputForIT].drug_id_38 and Y.market_share_percent >=  [BidFileTable-OutputForIT].market_share_pct) AS [market_share_rank]
FROM [BidFileTable-OutputForIT]
ORDER BY [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct DESC;


you missed a dot . in here

[BidFileTable-OutputForIT]drug_id_38
should be
[BidFileTable-OutputForIT].drug_id_38

where Y.drug_id_38= [BidFileTable-OutputForIT].drug_id_38
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SharathData Engineer

Commented:
You missed a dot in the WHERE clause.
where Y.drug_id_38= [BidFileTable-OutputForIT]drug_id_38
fixed it.
SELECT [BidFileTable-OutputForIT].drug_id_38, 
       [BidFileTable-OutputForIT].market_share_pct, 
       (SELECT COUNT(*) 
          FROM [BidFileTable-OutputForIT] AS Y 
         WHERE Y.drug_id_38 = [BidFileTable-OutputForIT].drug_id_38 
           AND Y.market_share_pct >= [BidFileTable-OutputForIT].market_share_pct) AS [Mkt Share Ranking] 
  FROM [BidFileTable-OutputForIT] 
 ORDER BY [BidFileTable-OutputForIT].drug_id_38, 
          [BidFileTable-OutputForIT].market_share_pct DESC;

Open in new window

Author

Commented:
Thanks Rey and Sharath

I got the query to work, but when I changed it to a make table query, the query would not finish making the table.

BidFileTable-OutputForIT has 34K records it has to sort through to create the table.  Would this many records be an issue for this type of query?

SELECT [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct, (select count(*) from [BidFileTable-OutputForIT] as Y where Y.drug_id_38= [BidFileTable-OutputForIT].drug_id_38 and Y.market_share_pct >=  [BidFileTable-OutputForIT].market_share_pct) AS market_share_rank1 INTO tbl_TEMP_DrugId38MktShare
FROM [BidFileTable-OutputForIT]
ORDER BY [BidFileTable-OutputForIT].drug_id_38, [BidFileTable-OutputForIT].market_share_pct DESC;
Top Expert 2016

Commented:
as i've mentioned above
it is not advisable to store calculated values to the table, you can always get the correct values running the query in case the values for the Mkt Share Percent changed

Author

Commented:
I understand and agree.  However, my current need is to have the percent and ranking stored.
SharathData Engineer
Commented:
34K records are not many records. You can remove the ORDER BY clause from the query. Do you have index on drug_id_38 column?

Author

Commented:
Thank you both for your help in getting this to work!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial