Ranking In MS Access

Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.

However same result can be achieved using a correlated query.

e.g. Consider a dataset of student marks below.

Code:

---

Students            Marks
Tony                      34
Bob                      32
Thor                      48
Jack                      42
Tom                      41
Kate                      45
Sid                      26

---

Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

PHP Code:

---

SELECT Students,
       (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks >= T2.Marks) AS Rank 
FROM 
      [Table] AS T2 
ORDER BY Marks DESC 


---

If you are ranking about and wanking with data and need to break wind or break a tie, you can do something like this which uses another variable to "break the tie" when there are more than one "Marks" with the same count.

Code:

---

SELECT T2.Students,
      (SELECT COUNT(T1.Marks)
        FROM [Table] AS T1
        WHERE T1.Marks > T2.Marks OR (T1.Marks = T2.Marks AND T1.ID < T2.ID)) + 1 AS Rank
FROM [Table] AS T2
ORDER BY T2.Marks DESC, T2.ID;

---

This YouTube video also shows how to do it: https://youtu.be/9wlme8NcBC8






https://www.youtube.com/@alanelston2330/featured