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:

ID Salesperson Division    NumberSold
1  Robert      Electronics 99
2  Jenny       Electronics 54
3  Billy       Appliances  54
4  Karen       Appliances  102
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

SELECT 
   t1.Salesperson,
   t1.Division,
   t1.NumberSold,
   COUNT(*) + 1 AS Rank
FROM
   tblSales AS t1
 
   LEFT JOIN tblSales AS t2
      ON t1.NumberSold < t2.NumberSold
GROUP BY
   t1.Salesperson,
   t1.Division,
   t1.NumberSold

Results

Salesperson Division    NumberSold Rank
Robert      Electronics 99         2
Jenny       Electronics 54         3
Billy       Appliances  54         3
Karen       Appliances  102        1
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

SELECT 
   t1.Salesperson,
   t1.Division,
   t1.NumberSold,
   COUNT(*) AS Rank
FROM
   tblSales AS t1
 
   LEFT JOIN tblSales AS t2
      ON t1.NumberSold < t2.NumberSold OR
         (t1.NumberSold = t2.NumberSold AND
         t1.ID <= t2.ID)
GROUP BY
   t1.Salesperson,
   t1.Division,
   t1.NumberSold

Results

Salesperson Division    NumberSold Rank
Robert      Electronics 99         2
Jenny       Electronics 54         4
Billy       Appliances  54         3
Karen       Appliances  102        1
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

SELECT 
   t1.Salesperson,
   t1.Division,
   t1.NumberSold,
   COUNT(*) AS Rank
FROM
   tblSales AS t1
 
   LEFT JOIN tblSales AS t2
      ON t1.Division = t2.Division AND
         t1.NumberSold <= t2.NumberSold
GROUP BY
   t1.Salesperson,
   t1.Division,
   t1.NumberSold

Results

Salesperson Division    NumberSold Rank
Robert      Electronics 99         1
Jenny       Electronics 54         2
Billy       Appliances  54         2
Karen       Appliances  102        1
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.

[NeoPa]

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

[tooybitz]

any checked if the samples are correct?
I created the same table and ran the same queries but it seems to be incorrect.

[tooybitz]

This code worked for me that what was stated above:

SELECT Salesperson,Division,NumberSold,
       (SELECT COUNT(T1.NumberSold)
          FROM
                 [Table1] AS T1
         WHERE T1.NumberSold >= T2.NumberSold and T1.Division = T2.Division) AS Rank
FROM
      [Table1] AS T2
ORDER BY NumberSold DESC

[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.

[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

[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.

[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

[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.

[hvsummer]

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

[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.

[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.

[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.

[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.

[isladogs]

I realise this is an old thread but came across it in a link provided by Rabbit in this current thread: 30 Records per Report

Firstly, many thanks to Rabbit for the article. I have used the approach described above on many occasions and it works well.
In terms of options 2 & 3, I would like to mention the following:

a) The Serialize function can provide a rank order quickly for both small & large datasets:

Public Function Serialize(qryname As String, KeyName As String, keyValue) As Long
 
On Error GoTo Err_Handler
 
   'used to create rank order for records in a query
   'add as query field
   'Example Serialize("qry1","field1",[field1])
 
   Dim rst As DAO.Recordset
   Set rst = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
   rst.FindFirst Application.BuildCriteria(KeyName, rst.Fields(KeyName).type, keyValue)
       Serialize = Nz(rst.AbsolutePosition, -1) + 1
    rst.Close
 
   Set rst = Nothing
 
Exit_Handler:
   Exit Function
 
Err_Handler:
   MsgBox "Error " & Err.Number & " in Serialize procedure: " & Err.Description
   GoTo Exit_Handler
 
End Function

I have an article explaining its use together with an example app at Rank Order Queries

b) Allen Browne wrote about the use of subqueries for ranking in this article: Ranking queries in Access. Like any subquery, this method can be slow for large datasets as mentioned in rabbit's article