By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,708 Members | 1,473 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Ranking Queries in MS Access SQL

Rabbit
Expert Mod 10K+
P: 12,403
Introduction
A query to rank or number the results is often requested. In more powerful database management systems such as Microsoft SQL and DB2, there are often functions to do this. However, in Microsoft Access, no such function exists.

Options
In Access, there are several workarounds:
  1. A running sum in a report
  2. VBA code called in the SELECT clause
  3. A subquery in the SELECT clause
  4. A DCount in the SELECT clause
  5. Joining the table to itself and using a COUNT

Option 1 is probably the easiest if you want to number the rows consecutively but is the least flexible of the options.

Options 2, 3, and 4 require each row to be evaluated separately and can be slow for large data sets.

Option 5 is the most complicated to understand but can often be the most efficient. That is the option I will be discussing in this article.

Examples
Given the following table and data:
Expand|Select|Wrap|Line Numbers
  1. ID Salesperson Division    NumberSold
  2. 1  Robert      Electronics 99
  3. 2  Jenny       Electronics 54
  4. 3  Billy       Appliances  54
  5. 4  Karen       Appliances  102
  6. 5  Kim         Appliances  30
For the first example, let's say you want to rank all the salespeople by number of items sold, you can join the table to itself on the number sold and do a count.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) + 1 AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.NumberSold < t2.NumberSold
  11. GROUP BY
  12.    t1.Salesperson,
  13.    t1.Division,
  14.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         2
  3. Jenny       Electronics 54         3
  4. Billy       Appliances  54         3
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         5
Note that this gives ties the same rank. If what you want is to number the rows rather than rank them, you will need to use a unique field.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.NumberSold < t2.NumberSold OR
  11.          (t1.NumberSold = t2.NumberSold AND
  12.          t1.ID <= t2.ID)
  13. GROUP BY
  14.    t1.Salesperson,
  15.    t1.Division,
  16.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         2
  3. Jenny       Electronics 54         4
  4. Billy       Appliances  54         3
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         5
If you want to break out the rankings or numbering by grouping field(s), you can do that by including them in the JOIN clause.

Query
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    t1.Salesperson,
  3.    t1.Division,
  4.    t1.NumberSold,
  5.    COUNT(*) AS Rank
  6. FROM
  7.    tblSales AS t1
  8.  
  9.    LEFT JOIN tblSales AS t2
  10.       ON t1.Division = t2.Division AND
  11.          t1.NumberSold <= t2.NumberSold
  12. GROUP BY
  13.    t1.Salesperson,
  14.    t1.Division,
  15.    t1.NumberSold
Results
Expand|Select|Wrap|Line Numbers
  1. Salesperson Division    NumberSold Rank
  2. Robert      Electronics 99         1
  3. Jenny       Electronics 54         2
  4. Billy       Appliances  54         2
  5. Karen       Appliances  102        1
  6. Kim         Appliances  30         3
Note that this ranks from highest to lowest. Going from lowest to highest merely requires flipping the less than operator to a greater than operator.
Feb 9 '14 #1
Share this Article
Share on Google+
13 Comments


NeoPa
Expert Mod 15k+
P: 31,662
Where's the Like button when you need it :-)
Feb 9 '14 #2

P: 3
any checked if the samples are correct?
I created the same table and ran the same queries but it seems to be incorrect.
Dec 5 '14 #3

P: 3
This code worked for me that what was stated above:
Expand|Select|Wrap|Line Numbers
  1. SELECT Salesperson,Division,NumberSold,
  2.        (SELECT COUNT(T1.NumberSold)
  3.           FROM
  4.                  [Table1] AS T1
  5.          WHERE T1.NumberSold >= T2.NumberSold and T1.Division = T2.Division) AS Rank
  6. FROM
  7.       [Table1] AS T2
  8. ORDER BY NumberSold DESC
Dec 5 '14 #4

Rabbit
Expert Mod 10K+
P: 12,403
Thanks for pointing that out. Forgot that the equal sign was needed for the first and last example and a missing condition in the second example. They have now been fixed.

You should be careful with subquery rankings though. With large numbers of records, they can be very slow.
Dec 5 '14 #5

P: 3
This might be posting a little late but I found a way that is way way much faster:



SELECT
t1.Salesperson,
t1.Division,
t1.NumberSold,
(SELECT Count(*) As CountS FROM
(SELECT t1.Salesperson, t1.NumberSold
FROM [Table1] t2) As Alias1
WHERE Alias1.Salesperson = t1.Salesperson AND Alias1.NumberSold <= t1.NumberSold) as Rank
FROM Table1 t1
Sep 30 '15 #6

Rabbit
Expert Mod 10K+
P: 12,403
That's not faster. It's an option that is already discussed in the article. What you posted is option 3 in the article. And can be painfully slow for large datasets.
Sep 30 '15 #7

100+
P: 215
I've created the Extend Vlookup with multi condition in vba excel, I'll thinking about post it like you did here NeoPa lol
Oct 24 '15 #8

NeoPa
Expert Mod 15k+
P: 31,662
My only post in this thread was to say that I liked it HVSummer.

That said, if you feel you have an article in you for such a function then go for it (Article Guidelines).

It would need to be added into the Excel Insights section of course.
Oct 24 '15 #9

100+
P: 215
can you guy make a like button ? I really want to click on it :D
Oct 25 '15 #10

P: 3
That's not faster. It's an option that is already discussed in the article. What you posted is option 3 in the article. And can be painfully slow for large datasets.
It ran in seconds for my dataset of 500K records. The method described in the article was taking over 10 min when I stopped it.
Oct 26 '15 #11

Rabbit
Expert Mod 10K+
P: 12,403
I have not found that to be the case with large datasets unless the joins were set up incorrectly or when indexes are missing.

But of course indexes greatly affect any query and properly creating indexes is an art all on its own. The tables we work with are in the tens of millions to hundreds of millions of rows and our DBAs spend much of their time creating the proper indexes.
Oct 26 '15 #12

P: 3
@Rabbit
That's a crazy amount of data lol. I'm limited to MS Access only so maybe that's why the query wouldn't work that well. I'm lucky if I hit 2 million rows of data before splitting the tables up because of the limitations on Access. You guys are in the big leagues.
Oct 26 '15 #13

Rabbit
Expert Mod 10K+
P: 12,403
lol, we certainly deal with a large amount of data. We have a team of people whose entire job is to monitor query performance and create indexes and setting configurations to make them perform better. Most of my time is spent finding the query that works best with how the environment is set up. So there's a lot of back and forth between our 2 teams to maximize performance.

Also, while some of the users have Access front ends, most of the back end data is on SQL Server, which can certainly affect SQL performance.
Oct 26 '15 #14