Ranking Queries in MS Access SQL

Rabbit

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:
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
Results
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
Results
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
Results
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

NeoPa

Where's the Like button when you need it :-)

Feb 9 '14 #2

tooybitz

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

tooybitz

This code worked for me that what was stated above:

Dec 5 '14 #4

Rabbit

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

xlDude

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

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

hvsummer

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

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

hvsummer

can you guy make a like button ? I really want to click on it :D

Oct 25 '15 #10

xlDude

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

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

xlDude

@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

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