1

I am using the following code in ACCESS 2010 to rank ~30000 rows from a table. However it takes around 15 minutes to do the ranking (I have to do this for 5 more columns and it could easily take more than an hour). I need these columns for further processing and hence tried creating a new table. I have also tried appending and updating to existing tables. Everything takes the same time. Am I missing something obvious here that could make it work faster?

SELECT MasterTable.Sales, (SELECT Count(*)+1 as HowMany From MasterTable AS Dupe WHERE Dupe.Sales > MasterTable.Sales) AS SalesRank INTO tableRank FROM MasterTable;

Details on the MasterTable: 11 columns. 1 primary key (Text). 5 parameters (Sales SalesQty Profit Hits Cost, all numeric). Ranks of 5 parameters(like SalesRank and so on) 30,000 rows

Dinesh Sundar
  • 86
  • 6
  • Can you clarify what you mean by `Rank`? – Newd Jun 15 '15 at 15:24
  • By "Rank", I mean creating a new column with ranks 1 (highest sales) through maximum. – Dinesh Sundar Jun 15 '15 at 15:24
  • Can you post the basic details about your `MasterTable`? – Newd Jun 15 '15 at 15:28
  • Details on the `MasterTable`: 11 columns. 1 primary key. 5 parameters(like the `Sales`). Ranks of 5 parameters(like `SalesRank`) 30,000 rows – Dinesh Sundar Jun 15 '15 at 15:39
  • Sorry, I mean more so like what the column names are, and possibly the data types. – Newd Jun 15 '15 at 15:44
  • Details on the `MasterTable`: 11 columns. 1 primary key (Text). 5 parameters (`Sales` `SalesQty` `Profit` `Hits` `Cost`, all numeric). Ranks of 5 parameters(like `SalesRank` and so on, all numeric as well) 30,000 rows – Dinesh Sundar Jun 15 '15 at 15:53

1 Answers1

0

Did you try a left join with grouping? 

SELECT
    m1.Sales,
    COUNT(*) AS HowMany
FROM
    MasterTable AS m1
    LEFT JOIN MasterTable m2 ON m1.Sales <= m2.Sales
GROUP BY
    m1.Sales
JamieD77
  • 13,796
  • 1
  • 17
  • 27